SQL奇技淫巧
- 根据指定字段查找重复数据
使用GROUP BY和HAVING方法
举例：根据uuid字段，查找uuid字段出现重复的数据，并统计重复的数量
|
1 |
SELECT uuid,COUNT(*) FROM test GROUP BY uuid HAVING COUNT(*) > 1 |
- 对字段值进行字符替换
|
1 |
UPDATE `orders` SET mobile = REPLACE(mobile,'\-','') WHERE mobile LIKE '%-%' |
- 检查手机号是否正确
|
1 |
SELECT * FROM `orders` WHERE `mobile` NOT REGEXP '^[1][345678][0-9]{9}$' |
- 删除指定字段重复的记录
123DELETE FROM bm_order_log WHERE(order_id,details) IN (SELECT order_id, details FROM ( SELECT order_id, details FROM bm_order_log GROUP BY order_id, details HAVING COUNT(*)>1) a)AND id NOT IN ( SELECT MIN(id) FROM (SELECT MIN(id) AS id FROM bm_order_log GROUP BY order_id,details HAVING COUNT(*)>1 ) b)
