【Mysql】如何修改表字符集

  • yhdmy

    2017-06-14 23:52:57
  • MySQL
  • 原创
一、描述与分析
        对于已有数据的表要更改字符集，是不能直接通过”alter database character set …” or “alter table table name character set …” 来修改，因为这样操作是不会更新已有数据的字符集。它只会对后新记录有效。正确的操作是，将数据导出，经过适当的调整重新导入。

二、实验
       目标：将 latin1 字符集的数据库修改成 GBK 字符集
1.导出表结构  

  1. mysqldump -uroot -p --default-character-set=gbk -d sam> createtab.sql
参数说明：
default-character-set=gbk 表示设置以什么字符集连接，-d 表示只导出表结构，不导数据

2.修改createtab.sql脚本中字符集为新字符集

  1. [root@sam ~]# cat createtab.sql
  2. -- MySQL dump 10.13 Distrib 5.6.24, for Linux (x86_64)
  3. --
  4. -- Host: localhost Database: sam
  5. -- ------------------------------------------------------
  6. -- Server version 5.6.24-enterprise-commercial-advanced

  8. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  9. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  10. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  11. /*!40101 SET NAMES gbk */;
  12. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  13. /*!40103 SET TIME_ZONE='+00:00' */;
  14. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  15. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  16. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  17. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

  19. --
  20. -- Table structure for table `sam1`
  21. --

  23. DROP TABLE IF EXISTS `sam1`;
  24. /*!40101 SET @saved_cs_client = @@character_set_client */;
  25. /*!40101 SET character_set_client = utf8 */;
  26. CREATE TABLE `sam1` (
  27.   `id` int(11) DEFAULT NULL,
  28.   `name` varchar(20) DEFAULT NULL
  29. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 换成 gbk;
  30. /*!40101 SET character_set_client = @saved_cs_client */;
  31. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

  33. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  34. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  35. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  36. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  37. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  38. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  39. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

  41. -- Dump completed on 2017-06-14 22:39:07
3.数据无更新状态，导出数据

  1. [root@sam ~]# mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 sam>samdata.sql
参数说明：
--quick 该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行,并在输出前将它缓存到内存中
--no-create-info 不写重新创建每个转储表的 CREATE TABLE 语句
--extended-insert 使用包括几个VALUES列表的多行INSERT语法。这样使转储文件更小，加载文件时可以加速插入
--default-character-set=latin1 按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码

4.打开 samdata.sql,将SET NAMES latin1 修改成 SET NAMES gbk

  1. [root@sam ~]# cat samdata.sql
  2. -- MySQL dump 10.13 Distrib 5.6.24, for Linux (x86_64)
  3. --
  4. -- Host: localhost Database: sam
  5. -- ------------------------------------------------------
  6. -- Server version 5.6.24-enterprise-commercial-advanced

  8. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  9. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  10. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  11. /*!40101 SET NAMES latin1 改成 gbk*/;
  12. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  13. /*!40103 SET TIME_ZONE='+00:00' */;
  14. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  15. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  16. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  17. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

  19. --
  20. -- Dumping data for table `sam1`
  21. --

  23. LOCK TABLES `sam1` WRITE;
  24. /*!40000 ALTER TABLE `sam1` DISABLE KEYS */;
  25. INSERT INTO `sam1` VALUES (1,'a'),(2,'b'),(3,'c');
  26. /*!40000 ALTER TABLE `sam1` ENABLE KEYS */;
  27. UNLOCK TABLES;
  28. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

  30. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  31. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  32. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  33. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  34. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  35. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  36. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

  38. -- Dump completed on 2017-06-14 22:47:23
5.使用新的字符集创建新的数据库

  1. mysql> create database sam_gbk default charset gbk;
  2. Query OK, 1 row affected (0.00 sec)

6.创建表，执行createtab.sql

  1. [root@sam ~]# mysql -uroot -p sam_gbk<createtab.sql
7.导入数据，执行samdata.sql

  1. [root@sam ~]# mysql -uroot -p sam_gbk<samdata.sql
8.验证

  1. mysql> use sam_gbk
  2. mysql> show create table sam1\G;
  3. *************************** 1. row ***************************
  4.        Table: sam1
  5. Create Table: CREATE TABLE `sam1` (
  6.   `id` int(11) DEFAULT NULL,
  7.   `name` varchar(20) DEFAULT NULL
  8. ) ENGINE=InnoDB DEFAULT CHARSET=gbk
  9. 1 row in set (0.00 sec)

  11. ERROR:
  12. No query specified

  14. mysql> select * from sam1;
  15. +------+------+
  16. | id | name |
  17. +------+------+
  18. | 1 | a |
  19. | 2 | b |
  20. | 3 | c |
  21. +------+------+
  22. 3 rows in set (0.00 sec)
三、总结
       mysql 对字符集使用很是灵活，在变更过程中注意子集往超级转OK，相反会有问题，会丢失部分数据。还希望DBA们加以小心，再有就是在起初设计数据库时就应该想好使用哪些文字，选择最适当字符集。继续学习的道路，给自己点赞。 Where there is a will, there is a way.

请使用浏览器的分享功能分享到微信等