MySQL 获取字符串长度详解

MySQL 获取字符串长度详解

📏 核心长度函数对比

⚠️ 六大关键注意事项

1. 字符编码决定字节长度

-- 查看数据库编码
SHOW VARIABLES LIKE 'character_set%';

-- 不同编码下的长度差异
SET @str = '中国';
SELECT 
  LENGTH(@str),                     -- 6 (UTF8)
  LENGTH(CONVERT(@str USING latin1)); -- 2 (latin1)

2. NULL 值的特殊处理

SELECT 
  LENGTH(NULL),      -- NULL
  CHAR_LENGTH(NULL); -- NULL

3. 多字节字符陷阱

-- 中文字符（UTF8）
SELECT 
  LENGTH('数据库'),    -- 9 (3字×3字节)
  CHAR_LENGTH('数据库'); -- 3

-- Emoji字符（UTF8MB4）
SELECT 
  LENGTH('👍'),       -- 4 (UTF8MB4)
  CHAR_LENGTH('👍');  -- 1

4. 空格和特殊字符

SELECT 
  LENGTH('  '),        -- 2 (空格)
  LENGTH('\t'),        -- 1 (制表符)
  CHAR_LENGTH(' ');    -- 1

5. 数值类型的隐式转换

SELECT 
  LENGTH(12345),      -- 5 (转为字符串'12345')
  CHAR_LENGTH(100.00);-- 6 (转为'100.00')

6. 二进制数据长度

SELECT 
  LENGTH(BINARY 'abc'), -- 3
  LENGTH(X'41');        -- 1 (十六进制'A')

🔍 高级应用场景

1. 验证输入长度限制

-- 用户名长度验证（6-20字符）
SELECT *
FROM users
WHERE CHAR_LENGTH(username) BETWEEN 6 AND 20;

2. 检测多字节字符存在

-- 查找包含中文的记录
SELECT *
FROM products
WHERE LENGTH(product_name) != CHAR_LENGTH(product_name);

3. 优化存储空间

-- 计算平均名称长度（按字节）
SELECT AVG(LENGTH(name)) AS avg_byte_length
FROM customers;

-- 按字符长度分组统计
SELECT 
  CHAR_LENGTH(title) AS title_length,
  COUNT(*) AS count
FROM articles
GROUP BY title_length;

4. 截断超长字符串

SET @long_text = 'This is a very long text...';

-- 安全截取前100字符
SELECT 
  IF(CHAR_LENGTH(@long_text) > 100, 
    CONCAT(SUBSTRING(@long_text, 1, 97), '...'),
    @long_text) AS truncated_text;

🚀 性能优化指南

1. 索引使用原则

-- 前缀索引创建（按字节）
ALTER TABLE products ADD INDEX idx_name (name(20));

-- 按字符长度过滤优化
SELECT * FROM products 
WHERE CHAR_LENGTH(name) > 10; -- 全表扫描

-- 优化方案：添加虚拟列
ALTER TABLE products
ADD COLUMN name_char_len TINYINT 
  AS (CHAR_LENGTH(name)) VIRTUAL,
ADD INDEX idx_name_len (name_char_len);

2. 避免全表扫描

-- 低效查询（无法使用索引）
SELECT * FROM logs 
WHERE LENGTH(message) > 1000;

-- 优化方案：存储计算值
ALTER TABLE logs
ADD COLUMN msg_length SMALLINT 
  AS (LENGTH(message)) STORED,
ADD INDEX idx_msg_len (msg_length);

3. 内存配置优化

-- 增大排序缓冲区
SET sort_buffer_size = 8*1024*1024; 

-- 查看当前配置
SHOW VARIABLES LIKE 'sort_buffer_size';

📊 函数行为对比表

💡 最佳实践总结

1. 字符数 vs 字节数：

   • 显示用途 → CHAR_LENGTH()
   • 存储优化 → LENGTH()

2. 多语言系统：

   -- 始终指定字符集
   SELECT CHAR_LENGTH(_utf8mb4 '👨‍👩‍👧‍👦'); -- 1 (家庭emoji)
   

3. 列设计建议：

   CREATE TABLE users (
     id INT PRIMARY KEY,
     username VARCHAR(20) CHARACTER SET utf8mb4, -- 按字符限制
     bio TEXT
   );
   

4. 安全截断：

   -- 确保不超过字段限制
   INSERT INTO users (username)
   VALUES (SUBSTRING(input_name, 1, 20));
   

5. 性能关键点：

   • 避免在 WHERE 子句中直接使用长度函数
   • 对频繁查询的长度值使用物化列
   • 为长文本字段添加前缀索引