MySQL创建数据库

引言

新项目第一步往往是建库，CREATE DATABASE语法简单，但字符集和排序规则选错会在后期引发中文乱码、表情存不下、排序不一致等麻烦，最好在建库时一次定好。

基本建库

CREATE DATABASE mydb;

用默认字符集建库，生产环境不建议这样，最好显式指定。

指定字符集建库（推荐）

CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

utf8mb4支持完整Unicode和emoji，general_ci不区分大小写，绝大多数Web项目够用。

判断不存在再建

CREATE DATABASE IF NOT EXISTS mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

IF NOT EXISTS防止重复执行报错，初始化脚本常用。

查看建库语句

SHOW CREATE DATABASE mydb;

确认字符集、排序规则和预期一致。

修改已有库字符集

ALTER DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

只改库默认值，已有表和列的字符集不会跟着变，需逐表处理。

删除数据库

DROP DATABASE IF EXISTS mydb;

不可逆，删前必须备份，IF EXISTS避免报错中断脚本。

授权配合

CREATE USER 'appuser'@'localhost' IDENTIFIED BY '强密码';
GRANT ALL ON mydb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

建库后给专用账号授权，不用root跑业务。

注意事项

• 统一utf8mb4，别用过时的utf8（只占3字节）；
• 排序规则ci结尾不区分大小写，bin区分；
• 库名小写、下划线分隔，避免跨平台大小写坑；
• 业务账号最小权限，禁止给ALL on .；
• ALTER库字符集不改存量表，需单独刷表；
• 初始化脚本加IF NOT EXISTS保证可重复执行。

总结

CREATE DATABASE建库，生产显式指定utf8mb4和排序规则，IF NOT EXISTS让脚本可重复执行，SHOW CREATE核对，ALTER只改默认值，DROP不可逆先备份，配合专用账号最小授权。建库定好字符集能避免后期乱码，库名规范小写，权限按库授予，别用root直接跑业务。

MySQL 字符集设置

中文乱码是数据库开发常见问题，字符集设置一定要搞对。

1. 常用字符集

• utf8： 最多3字节，不支持 emoji
• utf8mb4： 最多4字节，支持 emoji，推荐用这个
• gbk： 中文编码，不推荐

2. 查看当前字符集

-- 查看数据库字符集
SHOW VARIABLES LIKE 'character_set%';

-- 查看排序规则
SHOW VARIABLES LIKE 'collation%';

3. 创建数据库时指定

CREATE DATABASE mydb 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

4. 创建表时指定

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5. 修改已有表字符集

-- 修改表字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;

-- 修改字段字符集
ALTER TABLE users MODIFY name VARCHAR(50) CHARACTER SET utf8mb4;

6. 连接时设置字符集

PHP 连接数据库后一定要设置：

$conn = mysqli_connect('localhost', 'user', 'pass', 'db');
mysqli_set_charset($conn, 'utf8mb4');

或者 SQL 语句：

SET NAMES utf8mb4;

7. 中文乱码排查

出现中文乱码，按顺序检查：

1. 数据库字符集是不是 utf8mb4
2. 表字符集是不是 utf8mb4
3. 字段字符集是不是 utf8mb4
4. 连接字符集是不是 utf8mb4
5. 前端页面编码是不是 utf-8

8. 常用排序规则

• utf8mb4_general_ci： 不区分大小写，常用
• utf8mb4_unicode_ci： 支持 Unicode，更准确
• utf8mb4_bin： 区分大小写，按二进制比较

总结

MySQL 字符集设置要点：

• 统一用 utf8mb4，不要用 utf8
• 数据库、表、字段都要设置
• 连接后要设置字符集
• 中文乱码逐层排查
• 推荐 utf8mb4_general_ci 排序

字符集统一，才能避免乱码问题。

前言

存个emoji表情，MySQL报错" Incorrect string value"，乱码了。这就是字符集没设对。

一、utf8和utf8mb4区别

注意： MySQL的utf8是阉割版，真正的UTF-8是utf8mb4。

为什么要用utf8mb4

存emoji表情（😀🍔🚀），utf8存不了，会报错。

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'content'

二、查看当前字符集

SHOW VARIABLES LIKE 'character_set%';

输出：

三、设置字符集

1. 改my.cnf

[mysqld]
# 服务器字符集
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

重启MySQL：

systemctl restart mysqld

2. 建库时指定

CREATE DATABASE mydb 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

3. 建表时指定

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4. 改已有库字符集

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

5. 改已有表字符集

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

四、连接字符集

连接时也要设对，不然还是乱码。

PHP连接时设置

mysqli_set_charset($conn, 'utf8mb4');

连接后执行SQL

SET NAMES utf8mb4;

五、collation排序规则

推荐用utf8mb4_general_ci，快。

六、常见问题

问题1：存emoji报错

原因： 字段是utf8不是utf8mb4。

解决： 改成utf8mb4。

问题2：中文乱码

原因： 连接字符集不对。

解决： 连接后执行 SET NAMES utf8mb4。

问题3：like查询不区分大小写

原因： 用了_ci结尾的排序规则。

解决： 改用_bin。

常见坑

坑1：只改了表，没改库

库是latin1，表是utf8mb4，导数据还是乱码。

解决： 库、表、连接都要统一字符集。

坑2：改了表字符集，数据还是乱

改了表，但旧数据已经存成latin1了。

解决： 需要转码：

ALTER TABLE t MODIFY c BLOB;
ALTER TABLE t MODIFY c TEXT CHARACTER SET utf8mb4;

坑3：utf8和utf8mb4混用

有的表utf8，有的表utf8mb4，联表查询报错。

解决： 全库统一用utf8mb4。

坑4：mysqlclient不支持utf8mb4

老版本客户端不支持utf8mb4。

解决： 升级MySQL客户端。

总结

记住三点：

1. 数据库、表、连接全部用utf8mb4
2. 排序规则用utf8mb4_general_ci
3. 存emoji必须用utf8mb4

别用utf8了，直接上utf8mb4。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定都遇到过中文乱码的问题。本来好好的中文，存到数据库里就变成问号或者乱码了。这其实很大概率就是字符集设置不对导致的。

很多新手同学不知道utf8和utf8mb4有什么区别，建库的时候直接用默认的utf8，结果存个emoji表情就报错了，或者存进去之后变成乱码。

这篇文章就详细讲讲MySQL字符集的设置方法，以及utf8和utf8mb4到底有什么区别，应该怎么选。

一、utf8和utf8mb4的区别

首先大家要搞清楚一个坑：MySQL里的utf8并不是真正的UTF-8。

MySQL里的utf8字符集，每个字符最多只能存3个字节。而真正的UTF-8编码，有些字符是占4个字节的，比如emoji表情（😀😂👍这些），还有一些生僻字。

所以如果你用utf8字符集，存emoji表情的时候就会报错，或者存进去之后变成乱码。

而utf8mb4才是真正的UTF-8，每个字符最多可以存4个字节，emoji表情、生僻字都能正常存。

总结一下：

• utf8：每个字符最多3字节，存不了emoji表情
• utf8mb4：每个字符最多4字节，完全兼容UTF-8，推荐使用

现在做新项目，直接用utf8mb4就对了，不要再用utf8了。

二、查看当前MySQL字符集设置

先看看你的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     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

重点看这几个：

• character_set_server：服务器默认字符集
• character_set_database：当前数据库的字符集
• character_set_client：客户端字符集
• character_set_connection：连接字符集
• character_set_results：查询结果字符集

最好这几个都统一成utf8mb4，这样就不会有乱码问题了。

三、新建数据库的时候指定utf8mb4

如果你要新建一个数据库，直接在建库的时候指定字符集：

CREATE DATABASE testdb 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

这样建出来的数据库默认就是utf8mb4字符集了。

新建表的时候也可以指定：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

四、修改MySQL默认字符集

如果你想把MySQL的默认字符集改成utf8mb4，可以修改my.cnf配置文件。

找到MySQL的配置文件 /etc/my.cnf，在 [mysqld] 下面添加：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

修改完之后重启MySQL服务：

systemctl restart mysqld

这样以后新建的数据库和表就默认都是utf8mb4字符集了。

五、修改已有数据库的字符集

如果你的数据库已经建好了，想改成utf8mb4，可以执行：

ALTER DATABASE testdb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

注意：这个只会修改数据库的默认字符集，已经建好的表的字符集不会变。

六、修改已有表的字符集

如果要修改某一张表的字符集，可以执行：

ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

这样整张表的所有字段都会改成utf8mb4字符集。

如果要批量修改整个数据库里所有表的字符集，可以生成一个SQL脚本：

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.tables
WHERE table_schema = 'testdb';

执行完之后会生成一堆ALTER TABLE语句，把这些语句复制出来执行就行。

七、连接的时候指定字符集

除了数据库和表的字符集，连接的时候也要指定字符集，不然还是可能会乱码。

用命令行连接的时候：

mysql -u root -p --default-character-set=utf8mb4

或者登录之后执行：

SET NAMES utf8mb4;

常见坑

坑1：用了utf8字符集，存emoji表情报错

很多同学建库的时候用的是utf8，结果存个emoji表情就报错了，或者存进去之后变成问号。这就是因为utf8最多只能存3个字节，emoji表情是4个字节的。

解决方法就是把字符集改成utf8mb4。

坑2：数据库字符集改了，但是表的字符集没改

很多同学改完数据库的字符集之后，以为就完事了，结果还是乱码。这是因为数据库的默认字符集改了，但是已经建好的表的字符集还是旧的。

一定要把表的字符集也一起改了，最好是整张表转成utf8mb4。

坑3：连接字符集不对，导致中文乱码

有时候数据库和表都是utf8mb4，但是查出来还是乱码。这一般是连接字符集不对。可以执行 SET NAMES utf8mb4 试一下，或者连接的时候加上 --default-character-set=utf8mb4 参数。

坑4：utf8mb4_general_ci和utf8mb4_unicode_ci选哪个

很多同学不知道这两个排序规则有什么区别。简单来说：

• utf8mb4_general_ci：速度快，但是对一些特殊字符的排序可能不太准确
• utf8mb4_unicode_ci：排序更准确，但是速度稍微慢一点

一般网站用utf8mb4_general_ci就够了，不用太纠结。

总结

MySQL字符集和utf8mb4的核心知识点：

1. MySQL的utf8不是真正的UTF-8，最多只能存3字节
2. utf8mb4才是真正的UTF-8，能存emoji表情，推荐使用
3. 新建数据库和表的时候直接用utf8mb4
4. 老的数据库如果要改，要同时改数据库和表的字符集
5. 连接的时候也要指定utf8mb4，不然还是会乱码

现在做新项目，直接全程用utf8mb4就对了，省得以后再改。

遇到问题加QQ23979811 协助处理