这篇文章主要介绍了使MySQL能够存储emoji表情字符的设置教程,关键在于utf8mb4字符集的设置,需要的朋友可以参考下
MySQL 需要支持 emoji 表情符号版本需要大于5.5.3,且字符集需要设置为utf8mb4 字符集。
utf8mb4和utf8到底有什么区别呢?原来以往的mysql的utf8一个字符最多3字节,而utf8mb4则扩展到一个字符最多能有4字节,所以能支持更多的字符集。
将Mysql的编码从utf8转换成utf8mb4。
需要 >= MySQL 5.5.3版本、从库也必须是5.5的了、低版本不支持这个字符集、复制报错
停止MySQL Server服务
修改 my.cnf或者mysql.ini
1
2
3
4
5
6
7
8
9
10
11
|
[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 Server、检查字符集。
查看服务器字符集设置
1
|
mysql> SHOW VARIABLES WHERE Variable_name LIKE
'character%'
OR Variable_name LIKE
'collation%'
;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
+--------------------------+--------------------+
| 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 |
+--------------------------+--------------------+
|
查看数据库字符集
1
|
mysql>
select
*
from
SCHEMATA
where
SCHEMA_NAME=
'ttlsa'
;
|
1
2
3
4
5
|
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def | ttlsa | utf8mb4 | utf8mb4_unicode_ci | NULL |
+--------------+-------------+----------------------------+------------------------+----------+
|
查看表字符集
1
|
mysql>
select
TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
from
information_schema.TABLES;
|
1
2
3
|
+--------------------+----------------------------------------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION |
+--------------------+----------------------------------------------------+--------------------+
|
查看列字符集
1
|
mysql>
select
TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME
from
COLUMNS;
|
1
2
3
|
+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+
|
转换字符集语句
最后,还是看看我开发的“短信定时提醒”小程序,你也可以做的更好,扫码试一下,
你也可以做的更好!
1
2
3
4
5
6
7
8
9
|
use information_schema;
SELECT
concat(
"ALTER DATABASE `"
,table_schema,
"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;"
)
as
_sql
FROM
`TABLES`
where
table_schema
like
"DB_NAME"
group
by
table_schema;
SELECT
concat(
"ALTER TABLE `"
,table_schema,
"`.`"
,table_name,
"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
)
as
_sql
FROM
`TABLES`
where
table_schema
like
"DB_NAME"
group
by
table_schema, table_name;
SELECT
concat(
"ALTER TABLE `"
,table_schema,
"`.`"
,table_name,
"` CHANGE `"
,column_name,
"` `"
,column_name,
"` "
,data_type,
"("
,character_maximum_length,
") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
)
as
_sql
FROM
`COLUMNS`
where
table_schema
like
"DB_NAME"
and
data_type
in
(
'varchar'
);
SELECT
concat(
"ALTER TABLE `"
,table_schema,
"`.`"
,table_name,
"` CHANGE `"
,column_name,
"` `"
,column_name,
"` "
,data_type,
" CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
)
as
_sql
FROM
`COLUMNS`
where
table_schema
like
"DB_NAME"
and
data_type
in
(
'text'
,
'tinyte
xt'
,
'mediumtext'
,'long
|