22. 常用 SQL 技巧和常见问题
1. 正则表达式的使用⚓
expr REGEXP pat, expr RLIKE pat
expr NOT REGEXP pat, expr NOT RLIKE pat
用于匹配单个字符串。REGEXP 在进行模式匹配时是不区分大小写的，除非使用二进制字符串进行比较。
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
-> 1 0
MySQL 使用C语言的转义语法。
| 正则表达式 | 说明 |
|---|---|
| […] | 匹配出括号内的任意字符。字符串expr尝试匹配pat中的任意一个字符，如果有一个字符能匹配上，则返回 1。 |
| [^…] | 匹配不出括号内的任意字符。下例中字符串expr中如果有任何一个字符匹配不上pat中的任意一个字符，则返回 0；如果全部都能匹配上，则返回 1。 |
| a(m) | 匹配 m 个 a |
| a(m,) | 匹配 m 个或更多个 a |
| a(m,n) | 匹配 m 到 n 个 a |
| a(,n) | 匹配 0 到 n 个 a |
MariaDB [MYISAM_TEST]> select 'efg' REGEXP "[^XYZ]",'X' REGEXP "[^XYZ]";
+-----------------------+---------------------+
| 'efg' REGEXP "[^XYZ]" | 'X' REGEXP "[^XYZ]" |
+-----------------------+---------------------+
| 1 | 0 |
+-----------------------+---------------------+
1 row in set (0.00 sec)
MariaDB [MYISAM_TEST]> select 'efg' REGEXP "[XYZ]",'X' REGEXP "[XYZ]";
+----------------------+--------------------+
| 'efg' REGEXP "[XYZ]" | 'X' REGEXP "[XYZ]" |
+----------------------+--------------------+
| 0 | 1 |
+----------------------+--------------------+
1 row in set (0.00 sec)
2. 用 RAND()提取随机行⚓
用来从数据表中随机抽取记录。
可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实就是ORDER BY RAND()能够把数据随机排序。
-- 对所有的数据随机排序
SELECT * FROM CHILD ORDER BY RAND();
-- 随机抽取一部分数据
SELECT * FROM CHILD ORDER BY RAND() LIMIT 2;
3. 大小写敏感性⚓
所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。大部分Unix区分大小写，windows系统不区分。
列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在UNIX 中对大小写敏感，但在 Windows 或 Mac OS X 中对大小写不敏感。
默认情况下，表别名在UNIX 中对大小写敏感，但在 Windows 或 Mac OS X 中对大小写不敏感。
在 MySQL 中如何在硬盘上保存、使用表名和数据库名由 lower_case_tables_name 系统变量决定， 可以在启动 mysqld 时设置这个系统变量。
| 值 | 说明 |
|---|---|
| 0 | 使用 CREATE TABLE 或 CREATE DATABASE 语句指定的大写和小写在硬盘上保存表名和数据库名。名称对大小写敏感。在 UNIX 系统中的默认设置就是这个值 |
| 1 | 表名在硬盘上以小写保存，名称对大小写敏感。MySQL 将所有表名转换为小写以便存储和查找。该值为 Windows 系统中的默认值 |
| 2 | 表名和数据库名在硬盘上使用 CREATE TABLE 或 CREATE DATABASE 语句指定的大小写进行保存，但MySQL 将它们转换为小写以便查找。InnoDB的表名以小写保存。此值只在对大小写不敏感的文件系统上适用。Mac OS X 系统中的默认值 |
注意：
- 在 UNIX 中将 lower_case_tables_name 设置为 1 并且重启 mysqld 之前，必须先将旧的数据库名和表名转换为小写。
- 尽管在某些平台中数据库名和表名对大小写不敏感，但是最好养成在同一查询中使用相同的大小写来引用给定的数据库名或表名的习惯。
4. 外键⚓
MySQL 的外键功能仅对 InnoDB 存储引擎的表有作用，其他类型存储引擎的表虽然可以建立外键，但是并不能起到外键的作用， 只作为备忘录或注释来提醒用户目前正定义的列指向另一个表中的一个列。