坪地网站建设怎么样,加盟创业商机网,堵博网站建设,广东建设厅官网本文未做特别说明的#xff0c;指同时适用于 MySQL 5.7 和 MySQL 8.x。 先说结论#xff1a;
数据库、表名#xff08;包括别名#xff09;、触发器名是否大小写敏感与操作系统以及 MySQL 配置有关。列名#xff08;包括别名#xff09;、索引、存储过程和事件名称大小写… 本文未做特别说明的指同时适用于 MySQL 5.7 和 MySQL 8.x。 先说结论
数据库、表名包括别名、触发器名是否大小写敏感与操作系统以及 MySQL 配置有关。列名包括别名、索引、存储过程和事件名称大小写不敏感。字段内容大小写是否敏感与字符集排序规则有关。
MySQL 大小写规则
在 MySQL 中数据库对应于数据目录中的目录。数据库中的每个表对应于数据库目录中的至少一个文件也可能更多具体取决于存储引擎。触发器也对应于文件。因此底层操作系统的大小写敏感度会影响数据库、表和触发器名称的大小写敏感度。这意味着此类名称在 Windows 中不区分大小写但在大多数 Unix 版本中区分大小写。一个值得注意的例外是 macOS它基于 Unix但使用不区分大小写的默认文件系统类型 (HFS)。
列、索引、存储过程和事件名称在任何平台上都不区分大小写列别名也不区分大小写。
lower_case_table_names 系统变量
表和数据库名称如何存储在磁盘上以及如何在 MySQL 中使用受到 lower_case_table_names 系统变量的影响但该变量不影响触发器标识符的大小写敏感性。
lower_case_table_names 在 Unix 上默认值为 0。在 Windows 上默认值为 1。在 macOS 上默认值为 2。
值含义0表和数据库名称使用以大小写敏感的形式存储在磁盘上名称比较比如查询时区分大小写。如果在文件名不区分大小写的系统例如 Windows 或 macOS上运行 MySQL则不应将此变量设置为 0 。如果在不区分大小写的文件系统上强制将变量 lower_case_table_names 设置为 0并在基于 MyISAM 数据引擎的表使用不同的字母大小写访问表名则可能会导致索引损坏。1表名称以小写形式存储在磁盘上名称比较不区分大小写。MySQL 在存储和查找时将所有表名转换为小写。此行为也适用于数据库名称和表别名。2表和数据库名称使用以大小写敏感的形式存储在磁盘上但 MySQL 在查找时将它们转换为小写。名称比较不区分大小写。这仅适用于不区分大小写的文件系统基于 InnoDB 数据引擎的表名和视图名以小写形式存储正如 lower_case_table_names 1 的情况。
另外需要注意的一点是在 MySQL 8 中 lower_case_table_names 变量只能在MySQL服务器初始化时配置初始化后不允许修改。所以如何配置该参数要提前规划好防止后续修改时需要数据迁移导致其他问题。
MySQL 的字符集与排序规则
通俗讲字符集就是字符码的集合在 MySQL 中字符集的选择影响字符码的存储字符集选择不好不仅影响存储连展示都会有问题例如乱码。
在业务中常用的字符集是 UTF-8 字符集MySQL 有两种这样的字符集utf8、utf8mb4它们的区别如下
utf8: 支持最长 3 Byte 的字符编码但一部分 UTF-8 的 4 Byte 编码不支持例如 emoji。utf8mb4支持最长 4 Byte 的字符编码。这是业务中使用最多的字符集从 MySQL 8.0 开始成为默认字符集。
而字符的排序规则决定了字符在比较、排序时以及大小写敏感的规则。涉及字符比较的操作均与其相关例如排序、分组、索引、比较、、等。
在 MySQL 中字符集的排序规则决定了字段是否大小写敏感。这些排序规则一般以相关的字符集名开始通常包括一个语言名并且以 _ci、_cs 或 _bin 结束 。
ci 为 case insensitive 的缩写即大小写不敏感。cs 为 case sensitive的缩写即大小写敏感。bin 将字符串中的每一个字符用二进制数据存储区分大小写。
举例来说当使用 utf8mb4 字符集默认的字段排序规则是 utf8mb4_general_ci即字符大小写不敏感。
-- 所有字段都是 utf8mb4 字符集且均使用 utf8mb4_general_ci 排序规则
create table user(id int(11) not null auto_increment,username varchar(127) default null,nickname varchar(127) default null,primary key (id),unique key uniq_idx_username (username)
) engineinnodb default charsetutf8mb4;我们可以插入几条数据进行测试
INSERT INTO user (username) VALUES (user);
INSERT INTO user (username) VALUES (User);
INSERT INTO user (username) VALUES (USER);使用查询语句查询 username 为全部小写的 user 的用户结果三条记录全部都查询到了这是因为此时所有字段是大小写不敏感的。
mysql SELECT username from user where username user;
----------
| username |
----------
| user |
| User |
| USER |
----------
3 rows in set那么我想要只搜索出 username 为 user 的用户该怎么做呢
解决方案就是使用 BINARY 关键字来指定字符字段的排序规则
mysql select * from user where BINARY username user;
--------------
| id | username |
--------------
| 1 | user |
--------------
1 row in set这种方式相对较简单不用改动表结构只需在需要区分查询的字段前加上关键字。但这种方式也是有缺点的每次写查询的时候都要注意加关键字而且可能需要改动较多的代码。
另一种方式是在建表时就对 username 字段进行限制
-- 均使用 utf8mb4_general_ci 排序规则
CREATE TABLE USER (id INT (11) NOT NULL auto_increment,username VARCHAR (127) BINARY NOT NULL,nickname VARCHAR (127) DEFAULT NULL,PRIMARY KEY (id),UNIQUE KEY uniq_idx_username (username)
) ENGINE INNODB DEFAULT charset utf8mb4;如果需要整个表都是大小写敏感的可以在创建表时使用 utf8mb4_bin 排序规则。
create table user(id int(11) not null auto_increment,username varchar(127) default null,nickname varchar(127) default null,primary key (id),unique key uniq_idx_username (username)
) engineinnodb default charsetutf8mb4 COLLATEutf8mb4_bin;
-- 指定 COLLATEutf8mb4_bin 即可一些想法
如何设置 lower_case_table_names 参数
对于 lower_case_table_names 参数设置为 0 可能会带来哪些问题呢
比如说一位同事创建了 Test 表另一位同事在写程序调用时写成了 test 表则会报错不存在更甚者可能会出现 TestDb 库与 testdb 库共存Test 表与 test 表共存的情况这样就更加混乱了。所以为了实现最大的可移植性和易用性我们可以采用一致的约定例如始终使用小写名称创建和引用库表。也可以将 lower_case_table_names 设为 1 来解决此问题。
另外上面提到了使用小写的表名这也是阿里的 Java 开发手册推荐的用法。但如果我们还要考虑其他数据库的兼容比如 Oracle 呢这时保持小写表名就不是一个好想法了。
因为在 Oracle 中如果表名和字段名在定义的时候是小写的那么 SQL 操作时候表名和字段名是需要用引号括起来的。
参考
MySQL 5.7 官方文档MySQL 8.2 官方文档MySQL 的字符集与排序规则关于 MySQL 库表名大小写问题MySQL 大小写参数 lower_case_table_names 学习MySQL 转 Oracle 遇到的问题表名长度及大小写问题
