MySQL字符集乱码总结
MySQL数据集概览
1）使用多种字符集存储字符串：default character set utf8mb4
2）使用多种排序规则比较字符串：collate utf8mb4_general_ci
3）在同一服务器、同一数据库、同一表中不同字段里混合具有不同字符集或排序规则的字符串。
4）在任何级别启用字符集和排序规则的规范。
更多请见官网文档：https://dev.mysql.com/doc/refman/8.4/en/charset-mysql.html
1、创建数据库、表
CREATE DATABASE IF NOT EXISTS testDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `testTable` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR (20) CHARACTER SET gbk DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
2、查看字符集
查看数据库编码： show create database db_name;
查看表编码： show create table tbl_name;
查看字段编码： show full columns from tbl_name; 或者 show full fields from tbl_name;
例如：
默认字符编码： EFAULT CHARACTER SET utf8mb3
校验字符编码： COLLATE utf8mb3_general_ci
表存储引擎：ENGINE=MyISAM AUTO_INCREMENT=83010 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
MariaDB [wordpress]> show create database wordpress; +-----------+--------------------------------------------------------------------------------------------------+ | Database | Create Database | +-----------+--------------------------------------------------------------------------------------------------+ | wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci */ | +-----------+--------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [wordpress]> show create table wordpress.wp_postmeta; +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | wp_postmeta | CREATE TABLE `wp_postmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_id` bigint(20) unsigned NOT NULL DEFAULT 0, `meta_key` varchar(255) DEFAULT NULL, `meta_value` longtext DEFAULT NULL, PRIMARY KEY (`meta_id`), KEY `post_id` (`post_id`), KEY `meta_key` (`meta_key`(191)) ) ENGINE=MyISAM AUTO_INCREMENT=83010 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)
3、修改字符集
// 修改数据库字符集
CREATE DATABASE db_name [ [ DEFAULT ] CHARACTER SET charset_name ] [ [ DEFAULT ] COLLATE collation_name ]
ALTER DATABASE db_name [ [ DEFAULT ] CHARACTER SET charset_name ] [ [ DEFAULT ] COLLATE collation_name ]
alter database db_name character set utf8mb4;
// 修改表字符集
CREATE TABLE tbl_name (column_list) [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]]
ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]
alter table db_name.tb_name convert to character set utf8mb4 collate utf8mb4_general_ci;
// 修改字段字符集
alter table table_name modify column_name varchar(255) character set utf8mb4 collate utf8mb4_general_ci;
// 修改指定表全部字段字符集
alter table table_name convert to character set utf8mb4 collate utf8mb4_general_ci;
MySQL 端配置
1. 修改my.ini 或 /etc/my.cnf文件
[mysql] 和 [mysqld]
default-character-set=utf8
说明：mysql修改连接字符集和校对规则，mysqld修改服务器字符集和校对规则，它会同时设置；
character_set_client
character_set_connection
character_set_results
查看当前服务器的字符集（character）和校对规则（collation）：
mysql> show variables like 'character%';
mysql> show variables like 'collation%';
MariaDB [mysql]> show variables like 'character%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.001 sec) MariaDB [mysql]> show variables like 'collation%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_unicode_ci | | collation_database | latin1_swedish_ci | | collation_server | utf8mb4_unicode_ci | +----------------------+--------------------+ 3 rows in set (0.001 sec)
2. 修改数据库和表的字符集和校队规则
例如：
-- Create Database.
drop database if exists wordpress;
create database wordpress DEFAULT CHARACTER SET utf8; #CHARSET=utf8
-- 角色表
create table HR_ROLE (
ID bigint not null auto_increment,
NAME varchar(20) not null unique,
primary key (ID)
) ENGINE=INNODB DEFAULT CHARACTER SET utf8; #CHARSET=utf8
MySQL字符集终极解决方案
MySQL是中小企业构建web应用的首选，特别是和PHP配合简直就是一对黄金搭档LNMP、LAMP，深受web开发人员的喜爱。但自从4.1以来MySQL加入了多字符集的支持，很多MySQL使用者发现中文居然不能使用了，显示变成了一堆乱码！以致于很多人还在使用3.24.58的老版本，最近上MySQL网站，发现居然不提供3.24版本的下载了，MySQL已经彻底放弃3.24版本了。
怎么会产生乱码现象的，怎么解决？只要翻下网上的解决方案，马上就可以得出答案：“在获得连接之后执行一句set names 'gb2312'”，但这样做的原因是什么呢？总结一下我的经验。
MySQL处理连接时，外部连接发送过来的SQL请求会根据以下顺序进行转换：
character_set_server // 服务端的数据库默认的字符集
character_set_client // 客户端连接请求所采用的字符集
character_set_connection // MySQL连接字符集，服务器处理时会把 character_set_client 转换为 character_set_connection
character_set_database // 当前数据库所采用的字符集（表，列），不同数据库字符集可能不同
character_set_results // 客户机显示所采用的字符集
character_set_system // 存储系统元数据的字符集，总是 utf8 或 utf8mb3，不需要设置
character_set_filesystem // 把操作系统上的文件名转化成此字符集，即把 character_set_client 转换 character_set_filesystem， 默认 binary 是不做任何转换的
character_sets_dir // 存储的字符集文件 /usr/share/mariadb/charsets/
产生乱码的根本原因：
1）客户端没有正确设置client字符集，导致原先的SQL语句被转换成connection所指字符集，而这种转换，是会丢失信息的，如果client是utf8格式，那么如果转换成gb2312格式，这其中必定会丢失信息，反之则不会丢失。一定要保证connection的字符集大于client字符集，才能保证转换不丢失信息。
2）数据库字体没有设置正确，如果数据库字体设置不正确，那么connection字符集转换成database字符集照样丢失编码，原因跟上面一样。
为什么set names 'gb2312'就可以了呢
set names 'gb2312'相当于这三条语句:
set character_set_client = gb2312;
set character_set_connection = gb2312;
set character_set_results = gb2312;
这样做的话，上述产生乱码的原因1就不存在了，因为编码格式都统一了，但是这样做并不是万金油。原因有：
1）你的client不一定是用gb2312编码发送SQL的，如果编码不是gb2312，那么转换成gb2312就会产生问题。
2）你的数据库中的表不一定是gb2312格式，如果不是gb2312格式而是其他的比如latin1，那么在存储字符集时会产生信息丢失。
综上，终极解决方案如下:
1.首先要明确你的客户端时候何种编码格式，这是最重要的（IE6一般用utf8，命令行一般是gbk，一般程序是gb2312)
2.确保你的数据库使用utf8 或 utf8mb4格式，很简单，所有编码通吃。
3.一定要保证connection字符集大于等于client字符集，不然就会信息丢失，比如：
latin1 < gb2312 < gbk < utf8
若设置set character_set_client = gb2312，那么至少connection的字符集要大于等于gb2312，否则就会丢失信息
4.以上三步做正确的话，那么所有中文都被正确地转换成utf8格式存储进了数据库，为了适应不同的浏览器，不同的客户端，可以修改character_set_results来以不同的编码显示中文字体，由于utf8是大方向，因此web应用推荐使用utf8 / utf8mb4 格式显示中文的
public function getConnection() {
if ($this->conn != null) {
$this->conn->query("set character_set_client = gb2312"); // 客户端使用gb2312格式
$this->conn->query("set character_set_connection = utf8"); // 连接字符集使用utf8格式
$this->conn->query("set character_set_results = utf8"); // 显示字符集使用utf8格式
return $this->conn;
}
}
Q: 在写一个查询条件时的问题：如想写一个字段中包含“李”字的所有记录 $str="李";
select * from table where field like '%$str%' ;
显示的记录中除了包含”李”字的记录，还有不包含“李”字的记录。为什么？
A: 在MySQL中，进行中文排序和查找的时候，对汉字的排序和查找结果是错误的。这种情况在MySQL的很多版本中都存在。如果这个问题不解决，那么MySQL将无法实际处理中文。
出现这个问题的原因是：MySQL在查询字符串时是大小写不敏感的，在编绎MySQL时一般以ISO-8859字符集作为默认的字符集，因此在比较过程中中文编码字符大小写转换造成了这种现象。
现在MySQL上遇到一个问题，我们的字符集是gb2312.在中文模糊查找时,会有不相关的结果集.
从问题的根本原因分析，还有下面的问题。例：
汉字“不”的第1、2字节ascii值分别为：178与187
汉字“安”的第1、2字节ascii值分别为：176与178
汉字“花”的第1、2字节ascii值分别为：187与168
聪明的人已经看出来了：在字符串“安花”中模糊查找字符“不”字时，MySQL系统也会认为两者匹配!
出现这个问题的原因是：MySQL在查询字符串时是大小写不敏感的，在编绎MySQL时一般以ISO-8859字符集作为默认的字符集，因此在比较过程中中文编码字符大小写转换造成了这种现象。
方法一:
解决方法是对于包含中文的字段加上"binary"属性（区分大小写），使之作为二进制比较，例如将"name char(10)"改成"name char(10) binary"。
方法二:
如果你使用源码编译MySQL，可以编译MySQL时使用--with--charset=gbk 参数，这样MySQL就会直接支持中文查找和排序了。
方法三:
可以使用 MySQL 的 locate 函数来判断。以上述问题为例,使用方法为:
SELECT * FROM table WHERE locate(field,'李') > 0;
方法四:
把Select语句改成：SELECT * FROM TABLE WHERE FIELDS LIKE BINARY '%FIND%'
升级的根本，如果想使用“正确”的字符集，还是先用mysqldump导出成文件，然后导入。
MySQL 字符集查询
1） status
mysql> status; -------------- mysql Ver 14.14 Distrib 5.1.54, for debian-linux-gnu (x86_64) using readline 6.2 Connection id: 74267 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.16-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 128 days 13 hours 4 min 59 sec Threads: 1 Questions: 356155 Slow queries: 2 Opens: 3975 Flush tables: 1 Open tables: 256 Queries per second avg: 0.032 --------------
说明： 通过 sudo yum install mysql-server 安装的mysql，默认client和conn为utf8编码，server和db为latin1编码，修改client和conn编码请继续下看。
2）show variables like 'collation_%';
mysql> show variables like 'collation_%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+
3）show variables like 'character_%';
mysql> show variables like 'character_%'; +--------------------------+----------------------------+ | 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/share/mysql/charsets/ | +--------------------------+----------------------------+
4） show create table table_name；
5） show full fields from table_name;
mysql> show full fields from user_info; +------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | uid | bigint(18) | NULL | NO | | NULL | | select,insert,update,references | | | mac_id | char(17) | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | | | name | varchar(50) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | nickname | varchar(50) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | gender | tinyint(1) | NULL | YES | | 0 | | select,insert,update,references | | | age | varchar(7) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | +------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
6）查看mysql支持的字符集：
show charset; 或 show char set; 或 show character set;
MariaDB [mysql]> show charset; +----------+-----------------------------+---------------------+--------+ | 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 | | utf8mb3 | UTF-8 Unicode | utf8mb3_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 | | utf16le | UTF-16LE Unicode | utf16le_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 | +----------+-----------------------------+---------------------+--------+ 40 rows in set (0.000 sec)
默认的字符集和排序比较字符集有四个级别：server, database, table, and column
设置了_ci ， 顾名思义 _ai也是包含的，显式不区分大小写，隐式重音不敏感。
设置了 _cs ，顾名思义_as也是包含的，显式区分大小写，隐式重音敏感。
| Suffix | Meaning |
|---|---|
| _ai | Accent insensitive 重音不敏感 |
| _as | Accent sensitive 重音敏感 |
| _ci | Case insensitive 不区分大小写 |
| _cs | case-sensitive 区分大小写 |
| _bin | Binary 二进制 |
MySQL 字符集修改
MySQL中默认字符集的设置有四级：服务器级、数据库级、表级 、字段级，注意前三种均为默认设置，并不代表你的字段最终会使用这个字符集设置。
MySQL中关于连接环境的字符集设置有 Client端，connection，results 通过这些参数，MySQL就知道你的客户端工具用的是什么字符集，结果集应该是什么字符集。这样MySQL就会做必要的翻译，一旦这些参数有误，自然会导致字符串在转输过程中的转换错误。基本上99%的乱码由些造成。
修改客户端，服务器级，数据库级方法如下：
（1） 使用超级用户root权限，打开
vim /etc/mysql/my.cnf
（2） 修改客户端级，在[client]下添加一行：
default-character-set=utf8mb4
[client] default-character-set=utf8mb4 port = 3306 socket = /var/run/mysqld/mysqld.sock
如果想修改client和conn为latin1，只需把utf8改为latin1，更多编码格式请见下面：show charset;
（3） 修改服务器级字符集，在[mysqld]添加两行：
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_general_ci # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock
status 查询结果发现：Server 和 Db 变成了 utf8mb4
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2
Connection id: 42
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.31-0ubuntu0.12.04.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 19 sec
Threads: 1 Questions: 130 Slow queries: 0 Opens: 239 Flush tables: 1 Open tables: 58 Queries per second avg: 6.842
--------------
collation 和 character 查询结果发现：collation_server 和 character_set_server 也都变成了 utf8mb4
mysql> show variables like 'character_%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb4 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ mysql> show variables like 'collation_%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +----------------------+--------------------+
（4）修改数据库字符集的两种方法
a）修改db.opt文件：
vim /var/lib/mysql/your_dbname/db.opt # your_dbname是自己数据库的名称
default-character-set=latin1 # character_set_database default-collation=latin1_swedish_ci # collation_database 修改为： default-character-set=utf8mb4 default-collation=utf8mb4_general_ci
修改后发现：
Db characterset 变为了 utf8
mysql> status; -------------- mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2 Connection id: 42 Current database: tvbss_01 Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.31-0ubuntu0.12.04.1 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 1 min 22 sec Threads: 1 Questions: 142 Slow queries: 0 Opens: 239 Flush tables: 1 Open tables: 58 Queries per second avg: 1.731 --------------
b）命令行修改：
mysql> use your_dbname;
mysql> alter database your_dbname character set utf8mb4;
mysql> alter database your_dbname default character set utf8mb4 collate utf8mb4_general_ci;
结果同上。且此时命令行也修改了 /var/lib/mysql/your_dbname/db.opt 文件的编码为utf8（同方法a）
MySQL 表、字段的字符集修改
1） 修改表的字符集：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE utf8_general_ci];
ALTER TABLE tbl_name default CHARACTER SET utf8mb4 [COLLATE utf8mb4_general_ci];
修改表字符集示例： 数据库表 tbl_name 从latin1 转为 utf8mb4
mysql> show create table db_name.tbl_name; +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user_info | CREATE TABLE `user_info` ( `uid` bigint(18) NOT NULL, `name` varchar(50) DEFAULT NULL, `nickname` varchar(50) DEFAULT NULL, `gender` tinyint(1) DEFAULT '0', `age` varchar(7) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +----------- mysql> alter table db_name.tbl_name convert to character set utf8mb4 collate utf8mb4_general_ci; mysql> show create table db_name.tbl_name; +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user_info | CREATE TABLE `user_info` ( `uid` bigint(18) NOT NULL, `name` varchar(50) DEFAULT NULL, `nickname` varchar(50) DEFAULT NULL, `gender` tinyint(1) DEFAULT '0', `age` varchar(7) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
2）修改表的字段字符集：
ALTER TABLE tbl_name CHANGE column_name_old column_name CHARACTER SET character_name [COLLATE utf8_general_ci];
ALTER TABLE tbl_name CHANGE column_name_old column_name CHARACTER(60) SET utf8mb4 [COLLATE utf8mb4_general_ci] not null;
修改表的字段字符集示例：
字段 name 从 utf8 转为 latin1
mysql> show full fields from db_name.tbl_name; +------------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | uid | bigint(18) | NULL | NO | | NULL | | select,insert,update,references | | | name | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | nickname | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | gender | tinyint(1) | NULL | YES | | 0 | | select,insert,update,references | | | age | varchar(7) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | +------------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ mysql> alter table db_name.tbl_name change name name varchar(50) character set latin1 collate latin1_swedish_ci; mysql> show full fields from db_name.tbl_name; +------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | uid | bigint(18) | NULL | NO | | NULL | | select,insert,update,references | | | name | varchar(50) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | nickname | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | gender | tinyint(1) | NULL | YES | | 0 | | select,insert,update,references | | | age | varchar(7) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | +------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
MySQL 连接数
1） 查看连接数
show variables like "max_connections";
MariaDB [mysql]> show variables like "max_connections"; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 2048 | +-----------------+-------+ 1 row in set (0.001 sec)
2） 修改连接数（命令），不用重启就生效
set global max_connections = 4098;
MariaDB [mysql]> set global max_connections = 4098; Query OK, 0 rows affected (0.000 sec) MariaDB [mysql]> show variables like "max_connections"; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 4098 | +-----------------+-------+ 1 row in set (0.001 sec)
3） 修改连接数（配置文件）
sudo vi /etc/mysql/my.cnf
1）去掉注释，修改为：
max_connections = 4098
2） 重启MySQL生效
说明：MySQL 4.1开始，对多语言的支持有了很大变化 (这导致了问题的出现)。尽管大部分的地方 (包括个人使用和主机提供商)，MySQL 3、4.0 仍然占主导地位；但 MySQL 4.1 乃至5.0是 MySQL 官方推荐的数据库，已经有主机提供商开始提供并将会越来越多；因为 latin1 在许多地方 (下边会详细描述具体是哪些地方) 作为默认的字符集，成功的蒙蔽了许多 PHP 程序的开发者和用户，掩盖了在中文等语言环境下会出现的问题。
MySQL 4.1开始把多国语言字符集分的更加详细，所以导致数据库迁移，或则dz论坛升级到4.0后（dz4.0开始使用gbk或utf-8编码）出现乱码问题。
MySQL 4.1的字符集支持(Character Set Support)有两个方面：字符集(Character set)和排序方式(Collation)。对于字符集的支持细化到四个层次: 服务器(server)，数据库(database)，数据表(table)和连接(connection)
MySQL 4.1 对于字符集的指定可以细化到一台机器上安装的 MySQL，其中的一个数据库，其中的一张表，其中的一栏字段，应该用什么字符集。但是，传统的 Web 程序在创建数据库和数据表时并没有使用那么复杂的配置，它们用的是默认的配置，那么，默认的配置从何而来呢？
1）编译 MySQL 时，指定了一个默认的字符集，这个字符集是 latin1；
2）安装 MySQL 时，可以在配置文件 (my.ini) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的；
3）启动 MySQL 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的；
此时 character_set_server 被设定为这个默认的字符集；
当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为 character_set_server；
当选定了一个数据库时，character_set_database 被设定为这个数据库默认的字符集；
在这个数据库里创建一张表时，表默认的字符集被设定为 character_set_database，也就是这个数据库默认的字符集；
当在表内设置一栏字段时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集；
这个字符集就是数据库中实际存储数据采用的字符集，mysqldump 出来的内容就是这个字符集下的；
当按照原来的方式通过PHP存取MySQL数据库时，就算设置了表的默认字符集为utf8并且通过UTF-8编码发送查询，你会发现存入数据库的仍然是乱码。问题就出在这个connection连接层上。
想要进行“正确”的存储和得到“正确”的结果，最方便的是在所有query开始之前执行一下：
SET NAMES 'gbk';
其中gbk是数据库字符集，它相当于下面的三句指令：
SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;
4.1和5.0默认使用的是latin1字符集(拉丁文、古罗马的、拉丁美洲的)，如果只想使用gbk字符集存储和获取数据，在编译mysql 4.1和 5.0的时候，需要注意在my.ini或者my.cnf中添加两处参数
[mysqld]
default-character-set=utf8mb4
#settings for clients (connection, results, clients)
[mysql]
default-character-set=utf8mb4
转换数据库字符集，两种方法
第一种、导出导入数据库更改存储字符集
主要是把数据库的字符集有latin1改为gbk、big5、utf8、utf8mb4; 以下操作必须拥有主机权限。
假设当前操作的数据库名为：database
1）导出，把数据导为mysql4.0的格式，具体的命令如下：
mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt databse > d4.sql
--default-characte-set 以前数据库的字符集，这个一般情况下都是latin1的，
--set-charset 导出的数据的字符集，这个可以设置为gbk、big5、utf8、utf8mb4
2）建库，使用下面语句新建一个GBK字符集的数据库（test）
CREATE DATABASE `d4` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
3）导入，把刚才导出的数据导入到当前的数据库中就ok了。
mysql -uroot -p --default-character-set=gbk -f d4<d4.sql
通过以上的导出和导入就把数据库的字符集改为正确的存储方式了。
其中d4为新建库的名称，d4.sql为导出文件的名字
但是这种方法，发现数据库数据存储量无端变大30%，真是郁闷
第二种、手动修改字符集编码
另外一种其实原理相同，但是需要手动操作，一般用于第一种方法失败后的选择
不过这种方法如果数据库很大，估计很难做，因为光打开文件就能让你死机
首先还是用phpmyadmin或者用mysql本身的dump导出 .sql文件
然后用UltraEdit打开你备份的所有xxxx.sql文件，查找 DEFAULT CHARSET=latin1
latin1这里也许是别的，反正是你不想要的，要转成gbk或者big5的字符集把这个替换为“空”
CREATE TABLE cdb_sessions (
sid char(6) character set latin1 collate latin1_bin NOT NULL default '',
ip1 tinyint(3) unsigned NOT NULL default '0',
ip2 tinyint(3) unsigned NOT NULL default '0',
ip3 tinyint(3) unsigned NOT NULL default '0',
ip4 tinyint(3) unsigned NOT NULL default '0',
uid mediumint(8) unsigned NOT NULL default '0',
username char(15) NOT NULL default '',
groupid smallint(6) unsigned NOT NULL default '0',
styleid smallint(6) unsigned NOT NULL default '0',
invisible tinyint(1) NOT NULL default '0',
`action` tinyint(1) unsigned NOT NULL default '0',
lastactivity int(10) unsigned NOT NULL default '0',
fid smallint(6) unsigned NOT NULL default '0',
tid mediumint(8) unsigned NOT NULL default '0',
nickname char(15) NOT NULL default '',
UNIQUE KEY sid (sid)
) ENGINE=HEAP MAX_ROWS=1000;
替换为
CREATE TABLE `cdb_sessions` (
`sid` char(6) binary NOT NULL default '',
`ip1` tinyint(3) unsigned NOT NULL default '0',
`ip2` tinyint(3) unsigned NOT NULL default '0',
`ip3` tinyint(3) unsigned NOT NULL default '0',
`ip4` tinyint(3) unsigned NOT NULL default '0',
`uid` mediumint(8) unsigned NOT NULL default '0',
`username` char(15) NOT NULL default '',
`groupid` smallint(6) unsigned NOT NULL default '0',
`styleid` smallint(6) unsigned NOT NULL default '0',
`invisible` tinyint(1) NOT NULL default '0',
`action` tinyint(1) unsigned NOT NULL default '0',
`lastactivity` int(10) unsigned NOT NULL default '0',
`fid` smallint(6) unsigned NOT NULL default '0',
`tid` mediumint(8) unsigned NOT NULL default '0',
`nickname` char(15) NOT NULL default '',
UNIQUE KEY `sid` (`sid`)
) TYPE=HEAP MAX_ROWS=2000;
这一步更为简单的办法就是删除掉关于cdb_sessions表的这一段，将来全新装一个d4，将这个表导出
将其内容复制，粘贴到 sql文件的最后面，保存后，再把这个sql文件导入到你的库中，就OK了
用这两种方法就可以很方便的把4.1和5.0的mysql数据库降级到4.0，简单的过程就是
A 导出4.1/5.0的库
B 进行处理，转换成gbk字符集
C 彻底卸载4.1或者5.0
D 安装4.0.26
E 然后导入处理完的库
降级的时候导出库可以用这个方法，这样导出的就是4.0的库
mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt databse --compatible=mysql40 > d4.sql
至于mysql版本的升级，
如果数据文件中有中文信息，那么将MySQL 4.0的数据文件，直接拷贝到MySQL 4.1中就是不可以的，即便在my.ini中设置了default-character-set为正确的字符集。虽然貌似没有问题，但MySQL 4.1的字符集有一处非常恼人的地方，以gbk为例，原本MySQL 4.0数据中varchar,char等长度都会变为原来的一半，这样存储中文容量不变，而英文的存储容量就少了一半。这是直接拷贝数据文件带来的最大问题。
所以，升级的根本，如果想使用“正确”的字符集，还是先用mysqldump导出成文件，然后导入。
utf8(utf8mb3)和utf8mb4 区别
1）字符集大小
UTF-8 支持基本的 Unicode 多语言平面 (BMP)，即从 `0x0000` 到 `0xFFFF` 的字符。它通常使用 1 至 4 个字节来表示这些字符。
UTF-8MB4 不仅支持 BMP 内的字符，还包括额外的辅助平面字符，覆盖范围是从 `0x0000` 到 `0x10FFFF` 的字符。这意味着它可以存储更长的字符串，尤其是那些超过 BMP 范围的字符，如表情符号等。
2）存储效率
使用 UTF-8 时，由于每个字符可能需要不同的字节数，存储效率可能会受到影响。
UTF-8MB4 作为 UTF-8 的超集，可以视为一种优化后的形式，它在存储效率方面优于 UTF-8，因为它允许相同的字符用相同的字节数来表示。
3）适用场景
UTF-8 通常被认为是一个“万能”的编码方式，适合大多数应用，尤其是在处理中文和其他主要语言的字符时。
UTF-8MB4 更适合于需要存储辅助平面字符的场景，如社交媒体平台或应用程序，因为它们经常与较长的字符串打交道，并且开始支持新的 Unicode 字符。
4）数据库支持
MySQL 从 5.5.3 版本开始支持 UTF-8MB4，这是为了更好地兼容未来的 Unicode 扩展和新出现的字符。
综上所述，虽然 UTF-8 已经广泛用于互联网上的文本传输和存储，但在需要存储更多 Unicode 字符或者希望提高存储效率的情况下，UTF-8MB4 是一个更好的选择。此外，考虑到数据库的长期可扩展性和对新字符的支持，使用 UTF-8MB4 可以避免未来可能的编码转换需求
5）字符集、连接字符集、排序字符集
utf8 是 MySQL 中的一种字符集，只支持最长三个字节的 UTF-8 字符（utf8mb3），也就是 Unicode 中的基本多文本平面。要在 MySQL 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持。为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 MySQL 官方建议，使用 VARCHAR 替代 CHAR。
utf8mb4对应的排序字符集有两种：utf8mb4_unicode_ci、utf8mb4_general_ci
utf8mb4_unicode_ci 和 utf8mb4_general_ci的对比：
准确性：
- utf8mb4_unicode_ci是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序
- utf8mb4_general_ci没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。
- 但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。
性能
- utf8mb4_general_ci在比较和排序的时候更快，推荐使用
- utf8mb4_unicode_ci在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。
- 但是在绝大多数情况下发，不会发生此类复杂比较。相比选择哪一种collation，使用者更应该关心字符集与排序规则在db里需要统一。
参考推荐：
版权所有: 本文系米扑博客原创、转载、摘录，或修订后发表，最后更新于 2024-07-15 10:32:41
侵权处理: 本个人博客，不盈利，若侵犯了您的作品权，请联系博主删除，莫恶意，索钱财，感谢！
转载注明: MySQL字符集乱码总结 （米扑博客）