您的位置:首页 - 文章 - Mysql - 正文

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,
本文原创,作者:西决,其版权均为品创网络所有。如需转载,请注明出处:https://www.sxpcwlkj.com/mysqlsb/

发表评论