Mysql 支持存储表情

今天移动端说服务端端无法存储表情字符，并且给了错误

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x80\xF0\x9F...' for column 'content' at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
....

查了下，原来存储表情需要使用到的utf8mb4的字符集，而原来字段的字符集只是utf8，于是变更了下字段

ALTER TABLE `question`   
  CHANGE `content` `content` VARCHAR(2500) CHARSET utf8mb4 COLLATE utf8mb4_general_ci NULL;

或者建表的时候就注意了

`content` VARCHAR(2500) CHARACTER SET utf8mb4 DEFAULT NULL,

再试了下，保存成功啦。另外这个字符集对mysql有要求，需要5.5.3及以上。

----

update 2017-12-14

排序规则，可以推出表编码规则

查看库

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "dbxx";

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

查看表

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = "dbname"
AND T.table_name = "tablename";

show table status from db_name;

查看列

SELECT character_set_name FROM information_schema.`COLUMNS`

WHERE table_schema = "dbname" AND table_name = "tablename" AND column_name = "columnname";

show full columns from tablexx;

jdbc 驱动不支持设置utf8mb4，设置成utf8可以自动支持utf8mb4，最低版本5.1.13

mysql 的utf8 只支持1~3个字节，实际意义上的utf-8可以支持1~4