MySQL字符集

MySQL

brew install mysql

brew services start mysql

brew services list

DB1

mysql -uroot -p

CREATE DATABASE IF NOT EXISTS db1
DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci;

USE db1;

CREATE TABLE t_demo (
id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
content varchar(255) NOT NULL
) DEFAULT CHARSET=utf8;

INSERT INTO t_demo ( content ) VALUES ( 'hello' );
# Query OK, 1 row affected (0.00 sec)

INSERT INTO t_demo ( content ) VALUES ( '😀' );
# ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'content' at row 1

TRUNCATE TABLE t_demo;

ALTER TABLE t_demo CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO t_demo ( content ) VALUES ( 'hello' );
# Query OK, 1 row affected (0.00 sec)

INSERT INTO t_demo ( content ) VALUES ( '😀' );
# ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'content' at row 1

my.cnf

vim /usr/local/etc/my.cnf

[client]
default-character-set=utf8mb4

[mysqld]
bind-address = 127.0.0.1

[mysql]
default-character-set = utf8mb4

brew services restart mysql

mysql -uroot -p

mysql> show variables like 'character_set_%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8mb4                                              |
| character_set_connection | utf8mb4                                              |
| character_set_database   | utf8                                                 |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8mb4                                              |
| character_set_server     | utf8                                                 |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /usr/local/Cellar/mysql/5.7.21/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

DB2

CREATE DATABASE IF NOT EXISTS db2
DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE db2;

CREATE TABLE t_demo (
id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
content varchar(255) NOT NULL
) DEFAULT CHARSET=utf8mb4;

INSERT INTO t_demo ( content ) VALUES ( 'hello' );
# Query OK, 1 row affected (0.00 sec)

INSERT INTO t_demo ( content ) VALUES ( '😀' );
# Query OK, 1 row affected (0.01 sec)

TRUNCATE TABLE t_demo;

ALTER TABLE t_demo CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO t_demo ( content ) VALUES ( 'hello' );
# Query OK, 1 row affected (0.00 sec)

INSERT INTO t_demo ( content ) VALUES ( '😀' );
# ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'content' at row 1

如果使用远程MySQL服务, 可以将端口映射到本地: ssh -L 3306:127.0.0.1:3306 -l saas 192.168.56.222

参考

• 清官谈mysql中utf8和utf8mb4区别

• 开发中的编码 -- emoji开发实例

• What's the difference between utf8_general_ci and utf8_unicode_ci

• mysql使用utf8mb4经验吐血总结

• 修改MySQL的字符集为utf8mb4

• MySQL数据库字符集由utf8修改为utf8mb4一例