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

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

•  

• gongtaige

  1 票

• 

  45

 

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

 

 

 

评论 ( 0) •  举报 (0) •  分享 •  链接 • 2013-12-10　

 

添加评论...

 

1个答案

票 数 

• brayden

  0 票

• 

  6562

• 最佳答案

 

可以的. 参考 高性能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类型.

转载于:https://www.cnblogs.com/brayden/p/5262062.html