mysql数据库字符集相关操作(修改表字段编码,使其支持emoji表情)

时间:2021-12-23 02:07:04

普通的UTF8编码是不支持emoji表情插入的,会报异常:

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\xB0\xE5\x9C...' for column 'name' at row 1

将表字段的编码单独改成utf8mb4,即可支持emoji表情的插入。

修改的sql语句为

mysql> alter table user modify column name varchar(255) character set utf8mb4;

下面整理了常用的编码相关命令:

查看数据库默认字符集

mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

修改默认编码

mysql> set character_set_database=utf8;

默认编码含义:

  character-set-server/default-character-set:服务器字符集,默认情况下所采用的。

  character-set-client:客户端的字符集。客户端默认字符集。当客户端向服务器发送请求时,请求以该字符集进行编码

  character-set-results:结果字符集。服务器向客户端返回结果或者信息时,结果以该字符集进行编码。

注:

  1)创建数据库时,除非明确指定,这个数据库的字符集被缺省设定为character_set_server;

  2)选定数据库时,character_set_database 被设定为这个数据库默认的字符集;

  3)在数据库中创建表时,表默认的字符集被设定为 character_set_database;

  4)当在表内新增列时,除非明确指定,否则此列缺省的字符集就是表默认的字符集

JDBC连接语句

  URL=jdbc:mysql://localhost:3306/abs?useUnicode=true&characterEncoding=字符集

以指定编码创建数据库

mysql> CREATE  DATABASE  ms_db  CHARACTER SET  utf8  COLLATE utf8_general_ci;

查看当前数据库编码

mysql> status;
--------------
C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe Ver 14.14 Distrib 5.6.35, for Win64 (x86_64) Connection id: 12
Current database: mysql
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.35-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 47 min 12 sec Threads: 11 Questions: 1811 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 101 Querie
s per second avg: 0.639
--------------

修改指定数据库编码

mysql> alter database mydb character set utf-8;

查看表的编码(建表语句)

mysql> show create table user;

查看表结构(完整的列属性)

mysql> show full columns from User;