【mysql】常用命令 时间 ID 卡密 索引 重复 唯一

格式化时间

NOW()
返回 2023-02-27 13:42:57

唯一ID

replace(UUID(),"-","")
返回 bb8df3f6f2c911edafb9525400471d0b

卡密生成

INSERT INTO `api`.`km` (`aid`, `km`,`type`, `zt`,`maxip`,`maxmac`, `regtime`,`bz`) VALUES ('1',replace(UUID(),"-",""), '30', '1','100','1',NOW(),'');

INSERT INTO `user` (`uid`,`i`,`crtime`) VALUES (SUBSTRING(replace(UUID(),"-",""),1, 8),'0',NOW());

清空表 索引从1开始计数

DELETE FROM `user`;
ALTER TABLE user AUTO_INCREMENT = 1;

查看字段重复次数

SELECT lj, count(*) as num FROM lj GROUP BY lj HAVING count(*)>=1;

删除字段重复数据并保留一条

delete lj_copy1 from lj_copy1, ( SELECT min(id) id,lj FROM lj_copy1 GROUP BY lj HAVING count(*) > 1 ) t2 where lj_copy1.lj = t2.lj and lj_copy1.id > t2.id

字段值 取中间

查询
SELECT substring_index(substring_index(lj,'&buyParam=',-1),'_1_',1) FORM lj
更改
UPDATE lj SET spid=substring_index(substring_index(lj,'&buyParam=',-1),'_1_',1)

添加唯一约束

ALTER TABLE lj ADD UNIQUE (spid);