mysql 拉丁1 轉換成 utf8

標籤：mysql

本例子環境為centos 7.4 yum安裝 mariadb 5.5.56

環境準備：建立latin1編碼的庫，表，資料

mysql --default-character-set=latin1

show variables like ‘char%‘;

650) this.width=650;" src="https://s4.51cto.com/oss/201711/22/5aa88edd6aaad80fa52253a1bf77a43f.png" title="QQ圖片20171122112812.png" alt="5aa88edd6aaad80fa52253a1bf77a43f.png" />

create database latin;

show create database latin;

use latin

create table student (id int,name varchar(10),age int,class varchar(20));

insert into student values (1,‘張三‘,18,‘數學‘);

insert into student values (2,‘李四‘,28,‘語文‘);

show create table student;

select * from student;

650) this.width=650;" src="https://s2.51cto.com/oss/201711/22/e5647a87bb26387986fa00991040b763.png" title="QQ圖片20171122113014.png" alt="e5647a87bb26387986fa00991040b763.png" />

正式開始：

1備份資料

mysqldump --default-character-set=latin1 latin >latin.sql

2修改服務端設定檔

vi /etc/my.cnf  在[mysqld]段添加

character_set_server = utf8

3重新啟動服務生效

systemctl stop mariadb

systemctl start mariadb

4更改字元編碼為utf8

sed -n s/latin1/utf8/gp latin.sql

sed -i s/latin1/utf8/g latin.sql

5建立新庫匯入資料

mysql --default-character-set=utf8

show variables like ‘char%‘;

650) this.width=650;" src="https://s5.51cto.com/oss/201711/22/6769216433c8f11a993095f200e3a552.png" title="QQ圖片20171122112916.png" alt="6769216433c8f11a993095f200e3a552.png" />

create database utf8;

show create database utf8;

use utf8

source /root/latin.sql

6驗證

select * from student;

650) this.width=650;" src="https://s5.51cto.com/oss/201711/22/d5fb21ff2bfd724e88c76c87504b595e.png" title="QQ圖片20171122113038.png" alt="d5fb21ff2bfd724e88c76c87504b595e.png" />

mysql 拉丁1 轉換成 utf8