mysql修改表默认字符集可能带来的隐患_MySQL

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)

但是这里要注意已有内容编码转换要注意编码可转换，不会成为乱码