MySQL之对数据库库表的字符集的更改

本文详细介绍如何将现有数据库的字符集从latin1平滑迁移到GBK,包括导出表结构、编辑字符集、导出数据、修改数据库编码、重建表结构及导入数据的全过程。

数据字符集修改步骤:

对于已有的数据库想修改字符集不能直接通过 "alter database character set *"或 "alter table tablename character set *",这两个命令都没有更新已有记录的字符集,而只是对新创建的表或者记录生效。 已经有记录的字符集的调整,必须先将数据导出,经过修改字符集后重新导入后才可完成。 修改数据库默认编码: alter database [your db name] charset [your character setting]

下面模拟将latin1字符集的数据修改成GBK字符集的实际过程。

1、导出表结构 [root@master ~]# /opt/mysql/bin/mysqldump --default-character-set=latin1  -uroot -p -d dbname > alltable.sql

2、编辑alltable.sql将latin1改成GBK

3、确保数据库不再更新,导出所有数据 [root@master ~]# /opt/mysql/bin/mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 dbname > alldata.sql

参数说明: --quick:用于转储大的表,强制mysqldump从服务器一次一行的检索数据而不是检索所有行,并输出前CACHE到内存中。

--no-create-info:不创建CREATE TABLE语句。

--extended-insert:使用包括几个VALUES列表的多行INSERT语法,这样你更小,IO也小,导入数据时会非常快。

--default-character-set=latin1:按照原有字符集导出数据,这样导出的文件中,所有中文都是可见的,不会保存成乱码。

4、打开alldata.sql将set names latin1修改成set names gbk;

5、删库和建库: create database dbname default charset gbk;

6、创建表,执行alltable.sql mysql -uroot -p dbname < alltable.sql

7、导入数据 mysql -uroot -p dbname < alldata.sql 注意:选择目标字符集时,要注意最好大于等于源字符集(字库更大),否则,可能会丢失不被支持的数据。

具体操作如下:

更改前的数据:
[root@master mysql]# mysql -uroot -p123456 -e "show create database oldboy\G;show create table oldboy.student\G;set names latin1;select * from oldboy.student;\G"
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET latin1 */
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | ???       |
|  7 | 老男孩    |
+----+-----------+
导出表结构:
[root@master mysql]# /opt/mysql/bin/mysqldump --default-character-set=latin1 -uroot -p -d oldboy > zhulh/alltable.sql
查看导出内容:
[root@master mysql]# grep -E -v "#|\/|^$|--" zhulh/alltable.sql
DROP TABLE IF EXISTS `error_log`;
CREATE TABLE `error_log` (
  `error_message` char(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `s1` int(11) NOT NULL,
  PRIMARY KEY (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `s1` int(11) DEFAULT NULL,
  KEY `s1` (`s1`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t2` (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
修改latin1为utf8:
[root@master mysql]# sed -i "s/latin1/utf8/" zhulh/alltable.sql
[root@master mysql]# grep -E -v "#|\/|^$|--" zhulh/alltable.sql
DROP TABLE IF EXISTS `error_log`;
CREATE TABLE `error_log` (
  `error_message` char(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `s1` int(11) NOT NULL,
  PRIMARY KEY (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `s1` int(11) DEFAULT NULL,
  KEY `s1` (`s1`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t2` (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
确保数据库不再更新,导出所有数据
[root@master mysql]# /opt/mysql/bin/mysqldump --default-character-set=latin1 --quick --no-create-info --extended-insert  -uroot -p -B oldboy > zhulh/alldata.sql
修改latin1为utf8:(将SET NAMES latin1 修改成SET NAMES utf8)
[root@master mysql]# sed -i "s/latin1/utf8/" zhulh/alldata.sql
删除老库:
root@mysql5.6 01:09:24->drop database oldboy;
Query OK, 4 rows affected (0.20 sec)
新建新库:
root@mysql5.6 01:12:53->create database oldboy default charset utf8;
Query OK, 1 row affected (0.00 sec)
导入数据:
[root@master mysql]# mysql -uroot -p123456 oldboy < zhulh/alltable.sql 
Warning: Using a password on the command line interface can be insecure.
[root@master mysql]# mysql -uroot -p123456 oldboy < zhulh/alldata.sql 
Warning: Using a password on the command line interface can be insecure.
[root@master mysql]# mysql -uroot -p123456 -e "select * from oldboy.student;"
Warning: Using a password on the command line interface can be insecure.
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | ???       |
|  7 | 老男孩    |
+----+-----------+
[root@master mysql]# 

 

### 更改 MySQL 数据库字符集的方法 在 MySQL更改数据库字符集可以通过 SQL 语句完成,而使用 MySQL Workbench 可以更加直观地执行这些操作。以下是具体方法: #### 方法一:通过 SQL 语句修改字符集 可以使用 `ALTER DATABASE` 语句来更改现有数据库字符集及其排序规则。 ```sql -- 修改数据库 jwgl 的字符集为 utf8mb4 并设置排序规则为 utf8mb4_general_ci ALTER DATABASE jwgl CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ``` 此命令会更新数据库级别的字符集和排序规则[^3]。需要注意的是,这仅会影响后续创建的新或列,默认情况下不会自动转换已存在的数据中的字符集。 如果希望同时转换已有数据,则需要逐一对每张应用类似的 `ALTER TABLE` 操作。例如: ```sql -- 转换特定 tbl_example 的字符集 ALTER TABLE tbl_example CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ``` #### 方法二:利用 MySQL Workbench 图形界面调整字符集 1. **打开 MySQL Workbench** 登录到您的 MySQL 实例,并导航至目标数据库 (`jwgl`)。 2. **右键点击数据库名称** 在左侧的对象浏览器中找到 `jwgl` 数据库,单击鼠标右键并选择 “Alter Schema...”。 3. **编辑字符集选项** 在弹出窗口中可以看到关于该数据库的一些属性设定区域,在其中定位到 `Character Set` 和 `Collation` 字段。将其分别更改为所需的值(比如 `utf8mb4` 和 `utf8mb4_general_ci`),然后保存更改。 这种方法本质上还是调用了前述提到的那种SQL指令,但它简化了手动编写复杂脚本的过程[^2]。 另外提醒一点,当涉及到实际生产环境下的大规模改动前,请务必做好充分备份以防万一发生意外情况影响业务正常运行。 ### 注意事项 - 如果只是单纯变更数据库层面的编码而不涉及任何现存对象的话那么整个过程应该是非常快速简单的; - 对于已经包含了大量记录或者结构复杂的格来说可能还需要额外考虑兼容性和迁移策略等问题因为直接强制改变可能会引起一些预料之外的结果比如说乱码现象等等所以建议先测试再推广实施全局性的改造计划[^4]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值