Yuav ua li cas txuag Emoji rau hauv MySQL? Nkag mus rau hauv database Emoji qhia cov lus nug cim teeb meem cim

Yog tias Emoji qhia tau khaws cia hauv utf8 encodingMySQL databaselossis MariaDB, nws tsis tuaj yeem tso tawm.

Vim li cas Emoji qhia nkag mus rau hauv cov ntaub ntawv dhau los ua cov lus nug cim cim?

Vim tias Emoji kab lus yog 4-byte cov cim, thiab utf8 cim teeb tsuas yog txhawb nqa 1-3 bytes ntawm cov cim, Emoji kab lus tsis tuaj yeem sau rau hauv cov ntaub ntawv.

Yuav ua li cas los daws qhov teeb meem uas Emoji qhia nkag mus rau hauv cov ntaub ntawv dhau los ua cov lus nug cim cim?

Muaj 2 txoj kev daws teeb meem:

1. Sau ntawv manually, hloov cov cim plaub-byte nrog cov cim kev cai.
2. hloov kho MySQL Database cim teeb, hloov cov cim database los ntawm utf8 rau utf8mb4, txhawb 1-4 byte cim.

Qhov tseeb, thawj txoj kev ua haujlwm ntau dhau thiab tsis pom zoo.

Nws raug nquahu kom siv cov tshuaj thib ob, hloov kho MySQL database cim teeb.

Pib los ntawm MySQL 5.5.3 version, cov ntaub ntawv tuaj yeem txhawb nqa utf4mb8 cim teeb ntawm 4 bytes, thiab tus cwj pwm tuaj yeem muaj txog 4 bytes, yog li nws tuaj yeem txhawb nqa ntau tus cwj pwm, thiab tseem tuaj yeem khaws Emoji kab lus.

• Tom qab MySQL 5.5.3, koj tuaj yeem yooj yim seamlessly upgrade rau utf8mb4 cim teeb.
• Tib lub sijhawm, utf8mb4 yog sib xws nrog utf8 cim teeb.
• Lub encoding, txoj hauj lwm thiab cia ntawm utf8 cim yog nyob rau hauv utf8mb4.
• Ib yam li utf8 cim teeb, tsis muaj kev puas tsuaj rau cov ntaub ntawv uas twb muaj lawm.

VimphpmyadminRau cov lus tsim tshiab hauv cov ntaub ntawv tsim tshiab, lub cim cim encoding yog:Latin1

• Qhov encoding no tsis tuaj yeem ntxig cov lus Suav thiab Emoji;
• Koj yuav tsum tau hloov tus cwj pwm teeb encoding ntawm lub database cim teeb thiab rooj rau utf8mb4;
• Tom qab ntawd, rov pib MySQL, koj tuaj yeem ntxig Suav thiab Emoji kab lus.

MySQL Emoji cim teeb meem daws teeb meem

Kauj Ruam 1:Hloov kho MySQL configuration file my.cnf

• (Windows server yog my.ini,LinuxLub server yog my.cnf)
• my.cnf cov ntaub ntawv, feem ntau hauv/etc/mysql/my.cnfchaw.
• CWP Control Vaj Huam Sib LuagCov ntaub ntawv my.cnf, yog nyob rau hauv/etc/my.cnfchaw.

Tom qab koj pom nws, thov ntxiv cov ntsiab lus hauv qab no hauv peb ntu ▼

[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'

Kauj Ruam 2:Restart MySQL database

service mysqld restart

Kauj ruam 3: Txheeb xyuas cov cim teeb tsa dua thiab nkag mus ▼ hauv SQL hais kom ua kab

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_database';

Xyuas seb cov hauv qab no ▼

Nkag mus rau cov lus txib SQL nram qab no ▼

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

Xyuas seb nws puas yog raws li hauv qab no ▼

 +--------------------------+--------------------+

| 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 |

+--------------------------+--------------------+

Tom qab tsim cov ntaub ntawv tshiab hauv phpMyAdmin, koj yuav tsumSaib database code ▼

SHOW CREATE DATABASE db_name;

• Xws li:SHOW CREATE DATABASE test;

Yog tsis muaj database encodingutf8mb4, koj yuav tsum hloov kho cov cim database teem rauutf8mb4 .

Yuav hloov kho cov cim database li cas rau utf8mb4?

Kauj ruam 1: Nyem lub npe database tam sim no hauv phpMyAdmin, ces nyem SQL.

Kauj ruam 2: Nkag mus rau cov lus txib hauv qab no los hloov kho cov cim database teev rauutf8mb4▼

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8mb4

• Xws li:ALTER DATABASE test DEFAULT CHARACTER SET utf8mb4;

Kauj Ruam 3:Restart MySQL database

service mysqld restart

• Qhov ntawd yog nws.

Nov yog qee qhov piv txwv ntawm SQL commands:

Hloov cov cim cim teeb tsa thiab tag nrho cov cim kab ntawm lub rooj rau cov cim tshiab teeb ▼

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

• Xws li:ALTER TABLE logtest CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

Yog tias koj tsuas yog hloov kho cov cim cim ntawm lub rooj ▼

ALTER TABLE tbl_name  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

• Xws li:ALTER TABLE logtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

Hloov tus cwj pwm ntawm daim teb ▼

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name 

• Xws li:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

Saib cov lus code ▼

SHOW CREATE TABLE tbl_name;

Saib Field Codes ▼

SHOW FULL COLUMNS FROM tbl_name;

Saib cov lej database tam sim no ▼

SHOW VARIABLES LIKE 'character_set_%';

• Lub mysql-connector-java tsav pob tsuas txhawb utf5.1.13mb8 hauv 4+