程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 使MySQL可以或許存儲emoji臉色字符的設置教程

使MySQL可以或許存儲emoji臉色字符的設置教程

編輯:MySQL綜合教程

使MySQL可以或許存儲emoji臉色字符的設置教程。本站提示廣大學習愛好者:(使MySQL可以或許存儲emoji臉色字符的設置教程)文章只能為提供參考,不一定能成為您想要的結果。以下是使MySQL可以或許存儲emoji臉色字符的設置教程正文


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

[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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved