MySQL大小写敏感配置|极客笔记

MySQL大小写敏感配置

1. 介绍

MySQL是一个广泛使用的开源关系型数据库管理系统，用于管理大量的结构化数据。在使用MySQL时，有时候需要考虑数据库中表名、字段名的大小写敏感性。本文将详细介绍MySQL的大小写敏感配置。

2. MySQL大小写敏感性

2.1 大小写敏感与大小写不敏感

在MySQL中，大小写敏感与大小写不敏感是指在比较字符串时是否区分大小写。例如，字符串 “apple” 与 “APPLE” 在大小写敏感的情况下被视为不同的字符串，而在大小写不敏感情况下被视为相同的字符串。

MySQL的大小写敏感性由以下两个配置项决定：

lower_case_table_names：该配置项决定数据库名和表名的转换方式，包括文件系统中使用的表空间文件名和在MySQL中使用的库名和表名。该值的不同设置会影响数据库名和表名是否区分大小写。
collation_server：该配置项决定了字符串比较时的排序规则和大小写敏感性。

2.2 配置项取值

2.2.1 `lower_case_table_names`

lower_case_table_names的取值范围如下：

0：表示表名和数据库名区分大小写。在Windows系统上，该值无效。在Linux和Mac OS X上，则对表名和数据库名区分大小写。
1：表示表名和数据库名不区分大小写。无论在哪种操作系统上，都会将表名和数据库名转换为小写存储。
2：表示表名和数据库名不区分大小写。无论在哪种操作系统上，都会将表名和数据库名转换为大写存储。

需要注意的是，一旦设置了lower_case_table_names的值，在创建数据库时必须遵守该规则，否则可能导致无法创建或访问数据库。

2.2.2 `collation_server`

collation_server的取值范围很广，根据具体需求选择适合的排序规则。其中，以 _ci 结尾的排序规则表示不区分大小写（ci即case-insensitive）。

常见的取值选项如下：

utf8_general_ci：UTF-8编码的通用排序规则，不区分大小写。
utf8_bin：UTF-8编码的二进制排序规则，区分大小写。
latin1_swedish_ci：Latin1编码的瑞典排序规则，不区分大小写。

需要根据具体的业务需求选择合适的排序规则。

3. 修改MySQL的大小写敏感配置

3.1 查看当前配置

在修改MySQL的大小写敏感配置之前，先要知道当前的配置。可以通过以下命令查看：

SHOW VARIABLES LIKE 'lower_case%';
SHOW VARIABLES LIKE 'collation_server';

示例输出：

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+

+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_server   | utf8_general_ci |
+--------------------+-----------------+

如示例所示，当前配置的 lower_case_table_names 为1，表示表名和数据库名不区分大小写；collation_server 为utf8_general_ci，表示不区分大小写。

3.2 修改配置文件

要修改MySQL的大小写敏感配置，可以通过修改配置文件来实现。配置文件的位置根据不同的操作系统有所不同，可以参考MySQL的官方文档查找具体位置。

3.2.1 Windows系统

在Windows系统上，MySQL的配置文件通常位于 C:\ProgramData\MySQL\MySQL Server x.y\my.ini，其中 x.y 表示MySQL的版本号。通过文本编辑器打开该文件，找到以下配置项：

[mysqld]
lower_case_table_names=1
collation-server=utf8_general_ci

可以根据需要修改对应的值，然后保存文件。

3.2.2 Linux和Mac OS X系统

在Linux和Mac OS X系统上，MySQL的配置文件通常是 /etc/my.cnf 或 /etc/mysql/my.cnf。使用文本编辑器打开该文件，找到以下配置项：

[mysqld]
lower_case_table_names=1
collation-server=utf8_general_ci

同样可以根据需要修改对应的值，然后保存文件。

3.3 重启MySQL服务

在修改了配置文件后，需要重启MySQL服务使配置变更生效。可以使用以下命令来重启服务：

sudo service mysql restart

4. 验证配置

修改完MySQL的大小写敏感配置后，可以进行验证以确认配置是否生效。

4.1 创建表和插入数据

首先，创建一个数据库和一个表，并插入一些数据。例如，创建以下表结构的 users 表：

CREATE DATABASE mydb;
USE mydb;
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20)
);
INSERT INTO users (username) VALUES ('John'), ('smith');

4.2 查询表数据

在默认情况下，MySQL的表名和数据库名是不区分大小写的。无论是 SELECT * FROM users 还是 SELECT * FROM USERS 都可以正确地查询到数据。

但如果将 lower_case_table_names 设置为0，表示表名和数据库名区分大小写。此时，SELECT * FROM users 和 SELECT * FROM USERS 将会查询到不同的结果。

4.3 修改配置后的查询

根据前面的配置修改，将 lower_case_table_names 设置为0，重启MySQL服务后，再次查询表数据，结果如下：

SELECT * FROM users;

输出：

+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | smith    |
+----+----------+

SELECT * FROM USERS;

输出：

ERROR 1146 (42S02): Table 'mydb.USERS' doesn't exist

在配置改为区分大小写后，USERS 表已经无法访问。

5. 总结

MySQL的大小写敏感配置通过 lower_case_table_names 和 collation_server 这两个配置项来实现。可以根据需要灵活调整，以满足业务的要求。

MySQL大小写敏感配置