mysql like不区分大小写|极客笔记

mysql like不区分大小写

在日常的数据库操作中，我们经常需要使用like语句来进行模糊匹配。通常情况下，like语句是区分大小写的，即大写字母和小写字母是不相等的。然而，在某些场景下，我们希望like语句不区分大小写。本文将详细介绍在MySQL中如何实现不区分大小写的like查询。

1. MySQL中like语句的基本用法

首先，我们先回顾一下MySQL中like语句的基本用法。like语句通常用于模糊匹配字符串。它使用通配符来代表字符串的一部分，可以进行更加灵活的查询。

下面是一个简单的示例，假设有一个表users，包含id和name两列，我们想要找到名字中包含”John”的所有用户，可以使用以下like语句：

SELECT * FROM users WHERE name LIKE '%John%'

上述语句中，%表示任意字符，所以'%John%'实际上代表了任意字符 + “John” + 任意字符的字符串。

2. MySQL中的大小写敏感问题

在MySQL中，默认情况下是区分大小写的，也就是说，like语句在匹配字符串时是区分大小写的。例如，如果我们执行以下查询语句：

SELECT * FROM users WHERE name LIKE '%john%'

那么只有名字中包含”john”的用户才会被查询出来，所有名字中包含”John”的用户将被忽略。

这在某些情况下可能会导致不便，特别是当我们希望查询结果不受大小写影响时。接下来，我们将介绍如何实现不区分大小写的like查询。

3. 使用COLLATE实现不区分大小写的like查询

在MySQL中，我们可以使用COLLATE关键字来实现不区分大小写的like查询。COLLATE关键字用于设定列的字符集排序规则。

我们可以使用COLLATE关键字将列的排序规则设置为不区分大小写，从而实现不区分大小写的like查询。下面是一个示例：

SELECT * FROM users WHERE name COLLATE utf8_general_ci LIKE '%john%'

上述语句中，utf8_general_ci表示使用UTF-8字符集，并设置排序规则为不区分大小写。这样，无论名字中的”john”是大写还是小写，都将被查询出来。

4. 在查询中使用LOWER函数

除了COLLATE关键字外，还可以使用LOWER函数将查询条件和列的值都转换为小写，从而实现不区分大小写的查询。

下面是一个示例：

SELECT * FROM users WHERE LOWER(name) LIKE LOWER('%john%')

在上述语句中，LOWER(name)将列name的值转换为小写，LOWER('%john%')将查询条件转换为小写。这样，不论名字中的”john”是大写还是小写，都将被查询出来。

5. 性能考虑

需要注意的是，在使用COLLATE关键字或LOWER函数进行不区分大小写的查询时，可能会影响到查询的性能。

首先，如果使用COLLATE关键字，MySQL在查询时必须使用指定的排序规则进行比较，这可能会导致性能下降。

其次，如果使用LOWER函数，MySQL在执行查询时会对每一行的值进行转换，这也会影响查询的性能。

因此，在实际开发中，我们需要根据具体情况权衡使用COLLATE关键字和LOWER函数的性能开销。

6. 示例代码

为了更好地理解不区分大小写的like查询的使用方法，下面提供一个示例代码：

-- 创建users表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- 插入测试数据
INSERT INTO users (name) VALUES ('John'), ('john'), ('Mary'), ('MARY');

接下来，我们将以不同的方式执行like查询：

-- 1. 使用默认区分大小写的like查询
SELECT * FROM users WHERE name LIKE '%john%';

-- 2. 使用COLLATE实现不区分大小写的like查询
SELECT * FROM users WHERE name COLLATE utf8_general_ci LIKE '%john%';

-- 3. 使用LOWER函数实现不区分大小写的like查询
SELECT * FROM users WHERE LOWER(name) LIKE LOWER('%john%');

运行以上代码，可以得到如下结果：

-- 1. 使用默认区分大小写的like查询
+----+------+
| id | name |
+----+------+
|  1 | John |
+----+------+

-- 2. 使用COLLATE实现不区分大小写的like查询
+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | john |
+----+------+

-- 3. 使用LOWER函数实现不区分大小写的like查询
+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | john |
+----+------+

可以看到，使用COLLATE关键字或LOWER函数都可以实现不区分大小写的like查询，而默认的区分大小写的查询只返回了符合大小写要求的数据。