http://www.dewen.net.cn/q/15720/这样的情况一个sql语句怎么写

这样的情况一个sql语句怎么写？

 

表messages中有mcnos字段（用户卡号，长度16），这个字段当初成设计成mediutext类型，绝大部分都是一个卡号，即长度为16位，但是也有一小部分长度为许多卡号拼凑的，大于16位。现在使用 like '%mcno%'这样的语句效率很低，我想让长度大于16为的用 like '%mcno%'匹配，长度等于16位的直接 ='mcno',请问，这样的情况一个sql可以完成吗？

 
 
 
评论 (0) • 举报 (0) •  • 链接 • 2013-12-10　
 
添加评论...
1个答案
票 数 

 

可以的. 参考 高性能mysql, Writing a lazy UNION 这一节, 给出sql如下:

  1.   SELECT GREATEST(@found := -1, id) AS id
  2.     FROM messages WHERE card_no = 'xxxxxxxxxxx'
  3. UNION ALL
  4.   SELECT id
  5.     FROM messages WHERE card_no like '%xxxxxxxxxxx%' AND @found IS NULL
  6. UNION ALL
  7.   SELECT 1 FROM DUAL WHERE ( @found := NULL ) IS NOT NULL;
 

解释一下:
第一个select:
"SELECT GREATEST(@found := -1, id) AS id FROM messages where id ='xxx'" 
--如果where 没有找到记录, 则select里 greatest函数不会被执行, 所以变量 @found为NULL; 如果where找到记录, 则select里 greatest函数 执行, 变量 @found被赋值为-1. 因为id都是正整数, 所以GREATEST(@found := -1, id)的值肯定是id;

第二个select:
-- @found为NULL则 此select不会执行, mysql引擎也不会去做 card_no like '%xxxxxxxxxxx%' 操作;

第三个select:
-- 重置@found变量.

首先用 '=' 方式做匹配, 好处是可以用到索引; 匹配不到再 'like' 操作. 因为'绝大部分都是一个卡号，即长度为16位', 所以性能还可以.

性能考虑

当然这里 like 需要走整个messages表, 而且不能索引. 所以还是很慢, 可以考虑更新一下表结构:

  1. 把卡号信息单独拿出来作为子表, 主表里只放卡号子表的id;
  2. 把 '一个卡号，即长度为16位' 放在一张表里;
  3. 把 '一小部分长度为许多卡号拼凑的，大于16位。'的数据 放在另一个表里.
 

这就很类似 <<高性能mysql>>, Writing a lazy UNION 这一节 给出的那个sql了, 分别从'users' 和'users_archived'两张表里查询 用户信息. 可以仔细揣摩一下.

PS: 没必要用mediumtext类型.

posted @ 2016-03-10 15:20  brayden  阅读(139)  评论(0)  编辑  收藏  举报