面试题：MySQL中比较规则对数据查询的影响

Question

在MySQL中，不同的比较规则（如utf8_general_ci和utf8_bin）如何影响字符串的查询？请举例说明如何在查询语句中利用这些规则来达到精确匹配或不区分大小写匹配的目的。

Answer 1

1. 比较规则对字符串查询的影响
   • utf8_general_ci：这是一种不区分大小写的比较规则。在这种规则下，MySQL在比较字符串时会忽略字母的大小写。例如，'Apple'和'apple'会被视为相等。这是因为在这种规则的字符集排序中，大写和小写字母被认为是等价的。
   • utf8_bin：这是一种二进制比较规则，它严格按照字符的二进制值进行比较，区分大小写。所以，'Apple'和'apple'在utf8_bin规则下是不相等的，因为它们每个字符的二进制表示不同。
2. 查询语句示例
   • 不区分大小写匹配（使用utf8_general_ci） 假设我们有一个表products，其中有一个product_name列。

-- 创建表
CREATE TABLE products (
    id INT,
    product_name VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8_general_ci;

-- 插入数据
INSERT INTO products (id, product_name) VALUES (1, 'Apple'), (2, 'Banana'), (3, 'apple');

-- 不区分大小写查询
SELECT * FROM products WHERE product_name LIKE 'apple';

• 精确匹配（使用utf8_bin）

-- 创建表，使用utf8_bin字符集
CREATE TABLE products_bin (
    id INT,
    product_name VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8_bin;

-- 插入数据
INSERT INTO products_bin (id, product_name) VALUES (1, 'Apple'), (2, 'Banana'), (3, 'apple');

-- 精确匹配查询
SELECT * FROM products_bin WHERE product_name LIKE 'Apple';

如果已经存在表，并且需要在查询时临时改变比较规则：

-- 使用utf8_general_ci不区分大小写查询
SELECT * FROM products WHERE product_name COLLATE utf8_general_ci LIKE 'apple';

-- 使用utf8_bin精确匹配查询
SELECT * FROM products WHERE product_name COLLATE utf8_bin LIKE 'Apple';