图片 3

MySQL插入汉语不乱码的5种艺术_Mysql_脚本之家,高档运营学习笔记

方法一:签到MySQL,先做 set names latin1
,然后在立异语句可能试行SQL语句

MySQL插入中文不乱码的5种艺术,mysql插入乱码5种

方法一:
报到MySQL,先做 set names latin1 ,然后在更新语句可能实践SQL语句

mysql> set names latin1;
mysql> source test.sql;

方法二: 在SQL文件中钦命set names latin1;然后登陆MySQL,实践相应文件

[r[email protected] ~]# cat test.sql 
set names latin1;
insert *****************;
mysql> source test.sql;

方法三:
在SQL文件中钦赐set names latin1;然后通过MySQL命令导入

[[email protected] ~]# mysql -uroot -p123456 test <test.sql

方法四: 通过点名MySQL命令的字符集参数落成–default-character-set=latin1

[[email protected] ~]# cat test.sql 
insert *****************;
[[email protected] ~]# mysql -uroot -p123456 --default-character-set=latin1 test <test.sql

艺术五:推荐此办法,然而提议接纳utf8
在安排文件里设置顾客端以致服务器端相关参数
即修改my.cnf 顾客端的模块参数,能够兑现set names utf8,且永久生效

[client]
 default-character-set=utf8 
 无需重启MySQL,退出当前登录,重新登录即可
[server]
 default-character-set=utf8 5.1以前的版本 
 character-set-server=utf8 5.5版本

库表,程序!
复制代码 代码如下:
CREATE DATABASE wyb  DEFAULT CHARACTER SET utf8 collate
utf8_general_cli;

字符集含义计算表

mysql> show variables like 'character_set%';

| character_set_client  | utf8  #客户端字符集      
| character_set_connection | utf8  #链接字符集      
| character_set_database | utf8  #数据库字符集,配置文件指定或者创建时指定      
| character_set_results | utf8  #返回结果字符集       
| character_set_server  | utf8  #服务器字符集,配置文件,或者创建库,表时候指定

正文出自 “crazy_sir” 博客

方法豆蔻梢头: 登入MySQL,先做 set names latin1 ,然后在立异语句恐怕实践SQL语句
mysql set names latin…

1.1 mysql插入汉语数据乱码

mysql> set names latin1;mysql> source test.sql;

1.1.1MySQL中增多普通话数据并查阅检表语句

方法二:在SQL文件中钦定set names latin1;然后登陆MySQL,推行相应文件

a.创制cuizhong测验数据库并查看建表语句

[root@localhost ~]# cat test.sql set names latin1;insert *****************;mysql> source test.sql;
mysql> create database cuizhong;
Query OK, 1 row affected (0.00 sec)
mysql> show create database cuizhongG
*************************** 1. row ***************************
   Database: cuizhong
Create Database: CREATE DATABASE `cuizhong` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

方法三:在SQL文件中钦定set names latin1;然后通过MySQL命令导入

提示:要是在设置MySQL时未钦命字符集,则mysql私下认可字符集是latin1。

[root@localhost ~]# mysql -uroot -p123456 test 方法四:通过指定MySQL命令的字符集参数实现--default-character-set=latin1 [root@localhost ~]# cat test.sql insert *****************;[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1 test 方法五:推荐此方法,但是建议使用utf8在配置文件里设置客户端以及服务器端相关参数即修改my.cnf 客户端的模块参数,可以实现set names utf8,且永久生效[client] default-character-set=utf8 无需重启MySQL,退出当前登录,重新登录即可[server] default-character-set=utf8 5.1以前的版本 character-set-server=utf8 5.5版本

b.在cuizhong库下开创四个student表,并查看表结商谈建表语句

库表,程序!复制代码 代码如下:CREATE
DATABASE wyb DEFAULT CHARACTE路虎极光 SET utf8 collate
utf8_general_cli;字符集含义总结表

mysql> use cuizhong
Database changed
mysql> show tables;
+--------------------+
| Tables_in_cuizhong |
+--------------------+
| student|
+--------------------+
1 row in set (0.00 sec)

mysql> create table student( id int(4) NOT NULL AUTO_INCREMENT, name char(20) NOT NULL, PRIMARY KEY(id) );
mysql> desc student
-> ;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra  |
+-------+----------+------+-----+---------+----------------+
| id| int(4)   | NO   | PRI | NULL| auto_increment |
| name  | char(20) | NO   | | NULL||
+-------+----------+------+-----+---------+----------------+
2    rows in set (0.00 sec)
mysql> show create table studentG
*************************** 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 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show variables like 'character_set%'; | character_set_client | utf8 #客户端字符集 | character_set_connection | utf8 #链接字符集 | character_set_database | utf8 #数据库字符集,配置文件指定或者创建时指定 | character_set_results | utf8 #返回结果字符集 | character_set_server | utf8 #服务器字符集,配置文件,或者创建库,表时候指定

提示:私下认可建表不钦点字符集则世袭库的字符集,即latin1。

本文出自 “crazy_sir” 博客

c.批量插入数据到student表

mysql> insert into student values(1,'zhangsan'),(2,'lisi'),(3,'wanger'),(4,'xiaozhang'),(5,'xiaowang');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
+----+-----------+
5 rows in set (0.00 sec)

提示:数字和保加利亚语的数量时符合规律的,不会有乱码难点。

d.插入一条中文数据

mysql> insert into student values(6,'我是谁');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from student;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
+----+-----------+
6 rows in set (0.00 sec)

现身难点:普通话内容乱码

何以插入粤语数据会现出乱码难点吧?

透过上面的事例大家得以观看客户端字符集和库,表字符集不相符以致乱码难点,所以我们在插入数据的时候要先查看系统字符集和顾客端,库表字符集是不是风华正茂律,不一致将字符集修改大器晚成致再插入数据。字符集不等同引致插入乱码解决办法删重新插入。

1.1.2 MySQL命令行插入闽南语数据不乱码实战

a.查看建表语句,注意默许的字符集是latin1

mysql> show create table studentG
*************************** 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=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

b.设置MySQL客商端的字符集和建表的字符集latin1生机勃勃致

安装字符集为插入数据表的字符集

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

c.再插入一条汉语数据

mysql> insert into student values(7,'小红');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红  |
+----+-----------+
7 rows in set (0.00 sec)

提示:不乱码了,不过老的数码就无法消亡了。

上面的是MySQL命令行插入数据不乱码的措施,那么只要更新的数目多就必要进行sql文件更新数据了,所以确定保证推行sql文件也不乱码怎么做呢?

1.1.3 推行SQL文件插入中文数据不乱码实战

a.将在更新的多个sql语句放在文本文件中如test.sql

mysql> system cat test.sql
set names latin1;
insert into student values(8,'不认识');

唤醒:必定要进入set names latin1,确定保障插入数据不乱码。

b.在MySQL命令行中通过source调用test.sql文件插入数据

mysql> source test.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红  |
|  8 | 不认识|
+----+-----------+
8 rows in set (0.00 sec)

计算:实施DQL,DML语句的时候要set name 系统及库表的字符集。

1.1.4 通过MySQL 命令加字符集参数导入数据解决乱码难点

a. 把要翻新的七个SQL语句放入文本中,这一次不带set names latin1

#set names latin1;
insert into student values(9,'李四');

b. 通过MySQL命令加上字符集参数钦定latin1字符集导入test.sql

[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1 cuizhong < test.sql

通过-e参数在mysql库外查看结果

[root@localhost ~]# mysql -uroot -p123456 -e "set names latin1;select * from cuizhong.student"
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红  |
|  8 | 不认识|
|  9 | 李四  |
+----+-----------+

1.1.5 MySQL插入中文不乱码5中艺术小结

艺术(1):登陆mysql先做然后再实行更新语句或实施语句文件

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> source test.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

升迁:确定保证test.sql文件格式正确

形式(2):在sql文件中内定set names
latin1;然后登陆mysql,通过如下命令奉行。

mysql> source test.sql
Query OK, 0 rows affected (0.00 sec)

措施(3):在sql文件中钦点set names latin1 然后经过mysql导入

[root@localhost ~]# mysql -uroot -p123456  cuizhong < test.sql
[root@localhost ~]# mysql -uroot -p123456 -e "set names latin1;select * from cuizhong.student"

提示:这里的cuizhong是库名不是表名。

措施(4):通过点名mysql命令的字符集参数来贯彻

#set names latin1;
insert into student values(9,'李四');
[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1 cuizhong < test.sql

办法(5)在布局文件里安装顾客端及服务端相关参数

a.校正my.cnf客商端模块的参数,能够兑现set names latin1效果,并永久生效

[client]
default-character-set=latin1

晋升:无需重启服务,退出重新登录生效。

b.在服务端里面再钦定latin1字符集

[mysqld]
Default-character-set=latin1适合5.1 及以前的版本
Default-character-server=latin1 适合5.5

1.2 Mysql数据库字符集知识

1.2.1 MySQL数据库字符集介绍

简单来说的说,朝气蓬勃套文字标志及其编码、比较准绳的群集。MySQL数据库字符集包罗字符集(CHARACTECRUISER)和核对法则(COLLATION)八个概念。此中,字符集是用来定义MySQL数据字符串的蕴藏形式,而查对准则则是概念相比较字符串的主意。前边建库的言辞中CHARACTE中华VSET latin1即为数据库字符集而COLLATE latin1_swedish_ci
为核查字符集,有关字符集详细内容参照他事他说加以考察mysql手册,第10张字符集章节。

1.2.2 MySQL数据库习认为常字符集介绍

选取MySQL时常用的字符集有下表三种

图片 1

1.2.3 MySQL怎样接受适当的字符集

a.如若管理百端待举的文字,发布到差异国度和地面,应选Unicode字符集。对mysql来讲就是UTF-8(每种汉字四个字节),若是运用需管理希腊语,有微量中中原人民共和国字UTF-8越来越好。

b.借使只需支撑汉语,並且数据量比比较大,品质供给也超高,可选GBK(定长,每一种汉字占双字节,罗马尼亚语也占双字节),假如急需多量运算,比较顺序等定长字符集越来越快,品质高。

c.管理移动网络业务,大概要求利用utf8mb4字符集。

1.2.4 查看当前MySQL系统扶持的字符集

[root@localhost ~]# mysql -uroot -p123456 -e "SHOW CHARACTER SET"

最常用的有四种:

[root@localhost ~]# mysql -uroot -p123456 -e "SHOW CHARACTER SET;"|egrep "gbk|utf8|latin1"|awk ' {print $0}'
latin1    cp1252 West European    latin1_swedish_ci    1
gbk    GBK Simplified Chinese    gbk_chinese_ci    2
utf8    UTF-8 Unicode    utf8_general_ci    3
utf8mb4    UTF-8 Unicode    utf8mb4_general_ci    4

翻开mysql当前的字符集设置境况

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

提醒:暗中认可情形下character_set_client,character_set_connection,character_set_results三者的字符集和系统的字符集是大同小异的,是还要改良的。即为:

[root@localhost ~]# cat /etc/sysconfig/i18n 
LANG="zh_CN.UTF-8"
[root@localhost ~]# echo $LANG
zh_CN.UTF-8

1.3 Mysql数据库默许设置的字符集是何等?

a.先看一下mysql默许情况下设置的字符集

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | gb2312   |
| character_set_connection | gb2312   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| gb2312   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

今是昨非字符集参数的意义如下

Variable_name  | Value  
| character_set_client | latin1  客户端字符集
| character_set_connection | latin1  连接字符集
| character_set_database   | latin1数据库字符集,配置文件指定或建库建表指定
| character_set_results| latin1  返回结果字符集
| character_set_server | latin1服务器字符集,配置文件指定或建库建表指定

更正linux系统字符集变量后,查看MySQL中字符集的生成

[root@localhost ~]# echo $LANG
zh_CN.UTF-8
[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

咱俩开掘character_set_connection,character_set_client,character_set_server
三者的字符集和类其余相符也都改成utf8了。

1.4 实践set names latin1到底做了如何

无论linux系统的字符集是gb2312依旧utf8暗中同意景况下插入数据都以乱码的。

a.当时翻开数据就是乱码

mysql> use cuizhong
Database changed
mysql> select * from student
-> ;
+----+---------------------+
| id | name|
+----+---------------------+
|  1 | zhangsan|
|  2 | lisi|
|  3 | wanger  |
|  4 | xiaozhang   |
|  5 | xiaowang|
|  6 | ??? |
|  7 | å°çº¢  |
|  8 | ä¸è®¤è¯†   |
|  9 | æŽå››  |
+----+---------------------+
9 rows in set (0.10 sec)

b. 实践完set对应的字符集操作,就解决乱码难点了

(1)先查看一下库和表的字符集

mysql> show create database cuizhongG
*************************** 1. row ***************************
   Database: cuizhong
Create Database: CREATE DATABASE `cuizhong` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)
mysql> show create table studentG
*************************** 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=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

(2)大家看库和表的字符集都是latin1,所以举行set names
latin1保证字符集相符就不会乱码了。

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红  |
|  8 | 不认识|
|  9 | 李四  |
+----+-----------+

(3)实践完set字符集操作的结果改正了之类字五个字符集character_set_client,character_set_connection,character_set_results的参数。

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| latin1   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

1.5 Mysql命令参数—default-character-set=latin1在做如何?

(1)先查看一下mysql的字符集

[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

(2)带—default-character-set=latin1 参数登陆mysql

[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.5.32 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> 

(3)今后再查看mysql的字符集

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| latin1   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

(4)带参数登入也许有的时候改动不带参数登入又变回去了

[root@localhost ~]# mysql -uroot -p123456 --default-character-set=latin1 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| latin1   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

1.6 确认保证MySQL数据库插入数据不乱码应用方案

1.6.1统生机勃勃MySQL数据库客商端及服务端的字符集

(1)MySQL数据库的上边多少个字符集(客商端和服务端)统百分之十一个字符集才能作保插入的国语数据库能够平常输出。当然,linux系统的字符集也要尽量和数据库字符集统大器晚成。

(2)show variables like ‘character_set%’;命令输出结果如下:

Variable_name| Value 
+--------------------------+--------------------------------+
①character_set_client | latin1  客户端字符集
②character_set_connection | latin1  连接字符集
③character_set_database   | latin1   数据库字符集
④character_set_results| latin1   返回结果字符集
⑤character_set_server | latin1   服务器字符集,配置文件制定或建库建表指定

个中,①②④四个参数暗中认可情形接收linux系统字符集设置,人工登入数据库执行set
names
latin1以至mysql钦定字符集登入操作,都以退换mysql顾客端的client、connection、results3个参数的字符集都为latin1,进而解决插入乱码难题,那个操作能够在my.cnf配置文件里更正mysql顾客端的字符集,配置情势如下:

[client]
Default-character-set=latin1
提示:不需要重启
[root@localhost ~]# sed -n "18,22p" /etc/my.cnf 
[client]
#password    = your_password
port        = 3306
socket        = /usr/local/mysql/tmp/mysql.sock
default-character-set = latin1
[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| latin1   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

(3)改正完顾客端字符集不用set查询表数据就不会乱码了

[root@localhost ~]# mysql -uroot -p123456 -e "select * from cuizhong.student;"
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红|
|  8 | 不认识 |
|  9 | 李四|
+----+-----------+

1.6.2 校订MySQL服务端字符集

(1卡塔尔(قطر‎ 按上边必要校订my.cnf参数

[mysqld]
Default-character-set = latin1适合5.1及以前版本

(2卡塔尔 改革前查看当前字符集

[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

(3卡塔尔(قطر‎ 查看修正的参数

[root@localhost ~]# sed -n "26,27p" /etc/my.cnf 
[mysqld]
character-set-server = utf8

(4卡塔尔(قطر‎ 重启mysql服务(分娩景况是不许重启的)

[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS!

(5卡塔尔 查看改过后的字符集

[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| 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 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

提示:以上在[mysqld]下设置的参数会更改上边2个参数的字符集设置。

| Variable_name| Value|
| character_set_database   | utf8 |
| character_set_server | utf8 |

其有时候我们再校正系统字符集mysql数据库字符集就不改了。

[root@localhost ~]# cat /etc/sysconfig/i18n 
LANG="zh_CN.GB2312"
#LANG="zh_CN.UTF-8"
[root@localhost ~]# source /etc/sysconfig/i18n 
[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| 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 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

1.6.3 统意气风发mysql数据库客商端及服务端字符集总括

不乱码观念:提议中葡萄牙语情状选用utf8
,linux系统,顾客端,服务端,库,表,程序字符集统生龙活虎。

1.Linux系统字符集统风度翩翩utf8

[root@localhost ~]# cat /etc/sysconfig/i18n 
LANG="zh_CN.UTF-8"

升迁linux客户款也要订正字符集 举个例子:xshell

图片 2

例如:SecureCRT

图片 3

2.Mysql数据库客户端

临时:

Set names latin1

永久:

改善my.cnf客商端模块的参数,能够完毕set names latin1效果,并永恒生效。

3.服务端

更改my.cnf参数

[mysqld]
Default-character-set = latin1适合5.1及以前版本
character-set-server = latin1适合5.5

4.库表,程序 内定字符集建库

Create database cuizhong_utf8 DEFAULT CHARACTER SET UTF8 COLLATE后面加校对规则

大家得以show一下查看帮助的查对准绳

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese| big5_chinese_ci |  2 |
| dec8 | DEC West European   | dec8_swedish_ci |  1 |
| cp850| DOS West European   | cp850_general_ci|  1 |
| hp8  | HP West European| hp8_english_ci  |  1 |
| koi8r| KOI8-R Relcom Russian   | koi8r_general_ci|  1 |
| latin1   | cp1252 West European| latin1_swedish_ci   |  1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |  1 |
| swe7 | 7bit Swedish| swe7_swedish_ci |  1 |
| ascii| US ASCII| ascii_general_ci|  1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci|  3 |
| sjis | Shift-JIS Japanese  | sjis_japanese_ci|  2 |
| hebrew   | ISO 8859-8 Hebrew   | hebrew_general_ci   |  1 |
| tis620   | TIS620 Thai | tis620_thai_ci  |  1 |
| euckr| EUC-KR Korean   | euckr_korean_ci |  2 |
| koi8u| KOI8-U Ukrainian| koi8u_general_ci|  1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |  2 |
| greek| ISO 8859-7 Greek| greek_general_ci|  1 |
| cp1250   | Windows Central European| cp1250_general_ci   |  1 |
| gbk  | GBK Simplified Chinese  | gbk_chinese_ci  |  2 |
| latin5   | ISO 8859-9 Turkish  | latin5_turkish_ci   |  1 |
| armscii8 | ARMSCII-8 Armenian  | armscii8_general_ci |  1 |
| utf8 | UTF-8 Unicode   | utf8_general_ci |  3 |
| ucs2 | UCS-2 Unicode   | ucs2_general_ci |  2 |
| cp866| DOS Russian | cp866_general_ci|  1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |  1 |
| macce| Mac Central European| macce_general_ci|  1 |
| macroman | Mac West European   | macroman_general_ci |  1 |
| cp852| DOS Central European| cp852_general_ci|  1 |
| latin7   | ISO 8859-13 Baltic  | latin7_general_ci   |  1 |
| utf8mb4  | UTF-8 Unicode   | utf8mb4_general_ci  |  4 |
| cp1251   | Windows Cyrillic| cp1251_general_ci   |  1 |
| utf16| UTF-16 Unicode  | utf16_general_ci|  4 |
| cp1256   | Windows Arabic  | cp1256_general_ci   |  1 |
| cp1257   | Windows Baltic  | cp1257_general_ci   |  1 |
| utf32| UTF-32 Unicode  | utf32_general_ci|  4 |
| binary   | Binary pseudo charset   | binary  |  1 |
| geostd8  | GEOSTD8 Georgian| geostd8_general_ci  |  1 |
| cp932| SJIS for Windows Japanese   | cp932_japanese_ci   |  2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |  3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)

5.支付顺序的字符集

简体UTF8

1.7 怎么样转移生产MySQL数据库库表的字符集

数码字符集的改变步骤

对于本来就有数据库想改进字符集不能够一直通过“alter database character set
”或者”alter table tablename character set
”,那三个指令都未曾立异原来就有多少的字符集。而只是对新创制的表或许数据生效。

现本来就有记录的字符集的调动必得将数据导出,经过更正字符集之后重新导入才可达成。

改过数据库私下认可编码

Alter database [your db name] charset [your character setting]

下边模拟将latin1字符集的数据库改善成GBK字符集的长河。

(1)导出表构造

Mysqldump –uroot –p123456 –-default-character-set=latin1 –d dbname>alltable1.sql –-default-character-set=gbk 表示以GBK字符集进行连接 –d只导表结构。

(2)然后编辑alltable.sql将latin1用sed替换到GBK

(3)确认保证数据不在更新导出全体数据

Mysqldmup –uroot –p123456 –-quick –-no-create-info –-extended-insert –-default-character-set=latin1 dbname>alltable.sql

参数表明:

–quick:用于转储大的表,压迫mysqldump从服务器一遍风姿罗曼蒂克行的追寻数据并不是寻觅全体行并出口前CACHE到内部存款和储蓄器中。

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

–extended-insert:使用包罗多少个VALUES列表的多行INSERT语法,那样文件越来越小,IO也小导入数据是丰盛快。

–default-character-set=latin1循序渐进原有字符集导出数据,那样导出的文件中,全体汉语都以可以预知的,不会保留成乱码。

(4)张开alltable.sql将set names latin1修正成set names
gbk(恐怕涂改系统的服务端和客户端卡塔尔

(5)建库

Create database dbname default charset gbk;

(6)创造表实践,alltable1.sql

Mysql –uroot –p123456 dbname<alltable1.sql

(7)导入数据

Mysql –uroot –p123456 dbname<alltable.sql

总结:latin1改成utf8

(1)建库及建表的口舌导出,sed批量改过为utf8。

(2)导出全体数据。

(3)改正mysql服务端和客商端编码为utf8。

(4)删除原有的库表及数据。

(5)导入新的建库建表的说话。

(6)导入mysql的持有数据。

发表评论

电子邮件地址不会被公开。 必填项已用*标注