Mysql随笔
1、Mysql 让主键 归0:
TRUNCATE TABLE 表名
有外键约束时
SET FOREIGN_KEY_CHECKS=0; TRUNCATE TABLE 表名 SET FOREIGN_KEY_CHECKS=1;
2、只清空数据表,主键不归0:
DELETE FROM 表名
3、排序
SELECT * FROM 表 ORDER BY 字段 DESC (DESC 降序 ASC 升序)
ORDER BY RAND() 随机排序
4、去重
group by [去重复字段表1,.....]
5、计算时间差
FROM `p_user_service` WHERE user_id=25 //返回天 SELECT TIMESTAMPDIFF(SECOND, `start_time`, NOW()) FROM `p_user_service` WHERE user_id=25 //返回秒差 SELECT TIMESTAMPDIFF(MINUTE, `start_time`, NOW()) FROM `p_user_service` WHERE user_id=25 //返回分钟差 SELECT TIMESTAMPDIFF(HOUR, `start_time`, NOW()) FROM `p_user_service` WHERE user_id=25 //返回时差 SELECT TIMESTAMPDIFF(DAY, `start_time`, NOW()) FROM `p_user_service` WHERE user_id=25 //返回天数差
6、MySQL导入大批量数据的时候超出了默认允许最大的数据包所以就提示2006 - MySQL server has gone away
max_allowed_packet=300M wait_timeout=200000 interactive_timeout = 200000
7、手机号加*处理
CONCAT_WS( '****', SUBSTRING(m.member_phone, 1, 4), SUBSTRING(m.member_phone, 9, 3) ) AS memberPhone,