MySQL语句工作点滴

1、添加Emoji表情，需要修改字段类型
注：textContent为存储表情字符的字段

ALTER TABLE feed CHANGE textContent textContent VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

ALTER TABLE `comment` CHANGE content content VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

2、随机生成点赞数

INSERT INTO z_likes(feed, photo, `user`, createdAt) VALUES(0, CONCAT('photo_', FLOOR(1 + (RAND() * 2351))), CONCAT('user_', FLOOR(1 + (RAND() * 29))), NOW());

INSERT INTO z_likes(feed, photo, `user`, createdAt) VALUES(CONCAT('feed_', FLOOR(1 + (RAND() * 15))), 0, CONCAT('user_', FLOOR(1 + (RAND() * 29))), NOW());

3、毫秒转日期

SELECT FROM_UNIXTIME(imgUrl_local / 1000, '%Y%m%d') AS da FROM album WHERE id = 1;

4、更新图片地址

UPDATE ad SET imgUrl = CONCAT('http://wubi.sostation.com/admin/upload/image/',FROM_UNIXTIME(REPLACE(imgUrl_local, '.webp', '') / 1000, '%Y%m%d'), '/', imgUrl_local), imgUrl_local = CONCAT(FROM_UNIXTIME(REPLACE(imgUrl_local, '.webp', '') / 1000, '%Y%m%d'), '/', imgUrl_local);

SELECT * FROM app_advertise WHERE TYPE IN (30, 99) ORDER BY modify_time DESC;

SELECT b.name, SUBSTRING_INDEX(b.app_imgs, '|', 1) FROM app_advertise a, app_info b WHERE a.type = 99 AND a.enter_param = b.id AND b.cpid = 92 AND a.adv_image NOT LIKE '%hm.play.cn%';
UPDATE app_advertise a, app_info b SET a.adv_image = SUBSTRING_INDEX(b.app_imgs, '|', 1) WHERE a.type IN (30, 99) AND a.enter_param = b.id AND b.cpid = 92 AND a.adv_image NOT LIKE '%hm.play.cn%';

5、MySQL中查询重复记录中的最大值并删除全部重复记录

DELETE FROM user_info WHERE id IN (
SELECT a.id FROM (
SELECT id FROM user_info WHERE device_id IN (SELECT device_id
FROM user_info 
GROUP BY device_id 
HAVING COUNT( * ) >1) AND id NOT IN (SELECT MAX(id) FROM user_info GROUP BY id HAVING COUNT(* )>1)) a);

6、删除重复记录，并保留重复数据中的一条数据

DELETE FROM site WHERE id NOT IN(SELECT * FROM(SELECT id FROM site GROUP BY f_index)AS b);

7、秒数 转 分秒-MP3时长

SELECT CONCAT(RIGHT(CONCAT('0', FLOOR(Duration / 60)), 2), ':', RIGHT(CONCAT('0', Duration % 60), 2)) FROM music_info WHERE Id = 1439;

8、时间与秒数互转

UPDATE hongbao.membership SET expiresAt = createdAt, expiresEnd = FROM_UNIXTIME(UNIX_TIMESTAMP(createdAt) + 1*24*3600) WHERE `level` = 1 AND expiresAt < createdAt;

9、替换回车换行符

UPDATE book_info SET book_description = REPLACE(REPLACE(book_description,CHAR(10),''),CHAR(13),'');