MySQL Modify character Set

MySQL database modify character set, introduce the method of modification

1) System Tools Iconv

#file filename#mysqldump--default-character-set=utf8 >20180523xxx.sql#file 20180523xxx.sql#iconv-t utf8mb4-c  20180523xxx.sql>20180523xxxutf8mb4.sql#file 20180523xxxutf8mb4.sql test mysqldump support where condition first test with a small file # Mysqldump  --where= "1=1 limit 10000" #转码之前要看是什么编码, only Chinese requires transcoding

2) Export Import sed

II) Export Import---sed1. export table structure modify character Set settings #mysqldump--default-character-set=utf8-d databasename > #createtab20180523. sql#sed-i S/charset=utf8/charset=utf8mb4/g  createtab20180523.sql 2. Export table data modification character Set #mysqldump--no-create-info databasename > Data20180523.sql#sed-i s/charset=utf8/charset=utf8mb4/g  data20180523.sql 3.create Database newdatabase Default CharSet utf8mb4#mysql  newdatabase < createtab20180523.sql#mysql newdatabase  < Data20180523.sql

3) ALTER TABLE's Way

CREATE TABLE T1 (  col1 char (Ten) CHARACTER set UTF8 COLLATE utf8_unicode_ci not NULL,  col2 CHAR (Ten) CHARACTER set U Tf8 COLLATE Utf8_bin not NULL) CHARACTER SET UTF8; #要修改字符集需要注意字段长度, and index length, ALTER TABLE locks table ALTER TABLE T1  DEFAULT characte R set UTF8MB4,  MODIFY col1 CHAR (Ten)    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not NULL,  MODIFY col2 CHA R (Ten)    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not NULL;

　　

MySQL Modify character Set