MySQL字符集
2018-04-16 本文已影响25人
诺之林
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
