| 您的当前位置：首页 --> MYSQL教程 |
| 使MySQL能够存储emoji表情字符的设置教程 |
| 浏览次数:1502 关键词 （ ） |
|
MySQL 需要支持 emoji 表情符号版本需要大于5.5.3，且字符集需要设置为utf8mb4 字符集。
将Mysql的编码从utf8转换成utf8mb4。 需要 >= MySQL 5.5.3版本、从库也必须是5.5的了、低版本不支持这个字符集、复制报错 停止MySQL Server服务 修改 my.cnf或者mysql.ini [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、检查字符集。 查看服务器字符集设置 mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character%' 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 | +--------------------------+--------------------+ 查看数据库字符集 mysql> select * from SCHEMATA where SCHEMA_NAME='ttlsa'; +--------------+-------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+-------------+----------------------------+------------------------+----------+ | def | ttlsa | utf8mb4 | utf8mb4_unicode_ci | NULL | +--------------+-------------+----------------------------+------------------------+----------+ 查看表字符集 mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.TABLES; +--------------------+----------------------------------------------------+--------------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | +--------------------+----------------------------------------------------+--------------------+ 查看列字符集 mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from COLUMNS; +--------------------+----------------------------------------------------+--------------------------------------------+--------------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME | +--------------------+----------------------------------------------------+--------------------------------------------+--------------------+ 转换字符集语句
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','tinytext','mediumtext','long
|
| 下载次数：19 |
| 下载地址：点击下载 |
| 本资源为程序自动采集，如有侵权请联系我们移除 admin#80vps.com 来信请将#替换为@ |
| 下一条 SQL计算timestamp的差值的方法 上一条 MySQL中查询日志与慢查询日志的基本学习教程 |
QQ:1668121212