-- 前綴匹配（走索引）
SELECT * FROM products 
WHERE name LIKE 'Apple%';

-- 后綴匹配（全表掃描）
SELECT * FROM logs 
WHERE path LIKE '%error.log';

-- 模糊匹配（慎用）
SELECT * FROM users 
WHERE phone LIKE '%135%';

-- 匹配單個(gè)字符
SELECT * FROM files 
WHERE name LIKE 'img_2023__%.jpg'; -- 匹配2023年任意月份圖片

-- 轉(zhuǎn)義特殊字符
SELECT * FROM content 
WHERE text LIKE '%100%%' ESCAPE ''; -- 查找包含"100%"的文本

-- 區(qū)分大小寫的匹配
SELECT * FROM accounts 
WHERE BINARY username = 'Admin'; -- 不會(huì)匹配'admin'

-- 驗(yàn)證郵箱格式
SELECT * FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

-- 提取復(fù)雜模式
SELECT * FROM logs 
WHERE message REGEXP 'ERROR #[0-9]{5}:';

-- 創(chuàng)建全文索引
ALTER TABLE articles ADD FULLTEXT(title, content);

-- 基礎(chǔ)搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('數(shù)據(jù)庫(kù)優(yōu)化');

-- 布爾模式（高級(jí)搜索）
SELECT * FROM articles 
WHERE MATCH(title, content) 
AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- 需配合ngram解析器
CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  FULLTEXT(title) WITH PARSER ngram
) ENGINE=InnoDB;

-- 中文檢索
SELECT * FROM books 
WHERE MATCH(title) AGAINST('程序設(shè)計(jì)');

-- 返回首次出現(xiàn)位置
SELECT *, LOCATE('緊急', title) AS pos 
FROM notices 
WHERE LOCATE('緊急', title) > 0;

-- 提取特定模式
SELECT order_no, 
       SUBSTRING(order_no, 5, 8) AS date_str 
FROM orders 
WHERE SUBSTRING(order_no, 1, 3) = 'CN';

-- 創(chuàng)建虛擬列
ALTER TABLE products 
ADD COLUMN brand_prefix VARCHAR(10) AS (SUBSTRING(name, 1, 4)) STORED;

-- 創(chuàng)建索引
CREATE INDEX idx_brand ON products(brand_prefix);

-- 快速查詢
SELECT * FROM products 
WHERE brand_prefix = 'Apple';

-- 創(chuàng)建反向索引
CREATE INDEX idx_reverse_name ON users (REVERSE(username));

-- 高效后綴匹配
SELECT * FROM users 
WHERE REVERSE(username) LIKE REVERSE('%@company.com');

-- 使用CRC32加速短文本匹配
CREATE TABLE urls (
  id INT PRIMARY KEY,
  url VARCHAR(255),
  url_crc INT AS (CRC32(url))
);

SELECT * FROM urls 
WHERE url_crc = CRC32('https://example.com') 
AND url = 'https://example.com';

-- 提取JSON字段值匹配
SELECT * FROM products 
WHERE JSON_UNQUOTE(JSON_EXTRACT(specs, '$.color')) = 'red';

-- JSON路徑搜索
SELECT * FROM configs 
WHERE JSON_CONTAINS_PATH(settings, 'one', '$.theme');

CREATE INDEX idx_text ON articles (text(20));

-- 反例：索引失效
SELECT * FROM users 
WHERE UPPER(name) = 'JOHN';

-- 正解：使用大小寫不敏感排序規(guī)則
CREATE TABLE users (
  name VARCHAR(50) COLLATE utf8_general_ci
);

-- 優(yōu)化前：低效后綴匹配
SELECT * FROM logs WHERE path LIKE '%.log';

-- 優(yōu)化后：利用反向索引
SELECT * FROM logs 
WHERE REVERSE(path) LIKE 'gol.%';

-- 提取匹配特征值到新列
ALTER TABLE products ADD COLUMN is_popular BOOL AS 
  (name LIKE '%Pro%' OR price > 1000);