UTF-8 encodes everything in the basic multilingual plane (i.e. U+0000 to U+FFFF inclusive) in 1-3 bytes. Therefore, you just need to check whether everything in your string is in the BMP.
In Java, that means checking whether any char
(which is a UTF-16 code unit) is a high or low surrogate character, as Java will use surrogate pairs to encode non-BMP characters:
publicstaticboolean isEntirelyInBasicMultilingualPlane(String text){for(int i =0; i < text.length(); i++){if(Character.isSurrogate(text.charAt(i))){returnfalse;}}returntrue;}
If you do not want to support beyond BMP, you can just strip those characters before handing it to MySQL:
publicstaticString withNonBmpStripped(String input ){if( input ==null)thrownewIllegalArgumentException("input");return input.replaceAll("[\\ud800-\\udfff]","");}
If you want to support beyond BMP, you need MySQL 5.5+ and you need to change everything that's utf8
to utf8mb4
(collations, charsets ...). But you also need the support for this in the driver that I am not familiar with. Handling these characters in Java is also a pain because they are spread over 2 chars
and thus need special handling in many operations.
原因是当前mysql的字符集为utf-8,最多3个字节,但遇到像emoji表情这样的特殊字符时,需要4个字节来表示。所以导致insert报错。
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+----------+-----------------------------+---------------------+--------+
解决办法:
1.备份数据库
2.升级Mysql Server到v5.5.3+
Upgrade the MySQL server to v5.5.3+
3.修改database,table,column字符集
# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)
4.修改my.ini(linux下为my.cnf)
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
重新启动Mysql,检查字符集:
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)
5.如果是用java连接的mysql,需要升级mysql-connector-java.jar至少到5.1.14
参考资料:
http://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4
http://technovergence-en.blogspot.jp/2012/03/mysql-from-utf8-to-utf8mb4.html
http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html
http://stackoverflow.com/questions/7814293/how-to-insert-utf-8-mb4-characteremoji-in-ios5-in-mysql
http://stackoverflow.com/questions/8709892/mysql-throws-incorrect-string-value-error