mysql修改表默認字符集可能帶來的隱患
我們在建表後如果後續需要修改表的默認字符集，可以通過ALTER TABLE來修改表的默認編碼
ALTER TABLE tablename DEFAULT CHARACTER SET utf8;
但是這樣有個問題，只改了表定義的默認編碼，對於每個列的已有字段的內容還是使用以前的編碼，已有表數據不會做編碼轉換。
mysql> create table mybig5 (id int not null auto_increment primary key,
-> subject varchar(100) ) engine=innodb default charset big5;
Query OK, 0 rows affected (0.81 sec)
mysql> show create table mybig5;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mybig5 | CREATE TABLE `mybig5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=big5 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table mybig5 default charset utf8;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
然後我們插入一個多字節的字符串記錄到表裡
mysql> INSERT INTO mybig5 VALUES (NULL, UNHEX('E7BB8FE79086'));
01:08:19 [INSERT - 0 row(s), 0.000 secs] [Error Code: 1366, SQL State: HY000] Incorrect string value: '\xE7\xBB\x8F\xE7\x90\x86' for column 'SUBJECT' at row 1
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
mysql> show create table mybig5;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mybig5 | CREATE TABLE `mybig5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(100) CHARACTER SET big5 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
我們可以看到列subject還是big5編碼，沒有改為utf8編碼。
我們可以使用2. ALTER TABLE tablename CONVERT TO CHARACTER SET utf8;來修改列的編碼
mysql> show create table mybig5;
mysql> +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-> | Table | Create Table |
-> +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-> | mybig5 | CREATE TABLE `mybig5` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `subject` varchar(100) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
-> +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-> 1 row in set (0.00 sec)
但是這裡要注意已有內容編碼轉換要注意編碼可轉換，不會成為亂碼
