mysql修改表、字段、库的字符集
mysql修改表、字段、库的字符集
修改数据库字符集:
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
把表默认的字符集和所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集:
ALTER TABLE test CONVERT TO CHARACTER SET character_name [COLLATE ...] 如:ALTER TABLE test CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;只是修改表的默认字符集:
ALTER TABLE test DEFAULT CHARACTER SET character_name [COLLATE...]; 如:ALTER TABLE test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;修改字段的字符集:
ALTER TABLE test CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...]; 如:ALTER TABLE test CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;查看数据库编码:
SHOW CREATE DATABASE db_name;
SHOW FULL COLUMNS FROM test ;查看表编码:
SHOW CREATE TABLE test;
查看字段编码:
SHOW FULL COLUMNS FROM test;
修改my.cnf
vi /etc/my.cnf
在[client]下添加
default-character-set=utf8
在[mysqld]下添加
default-character-set=utf8
重新启动MySQL
[root@bogon ~]# /etc/rc.d/init.d/mysql restart
查看字符集设置
SHOW VARIABLES LIKE 'character%';查看,发现数据库编码均已改成utf8
+--------------------------+---------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+--------------------------+---------------------------------+