mysql

MySql相关笔记：

 

1.修改字段字符集

ALTER TABLE `exhibit_likes` 
	MODIFY COLUMN `user_name` varchar(255) CHARACTER SET utf8mb4 NULL COMMENT '用户昵称'

 

 2.优化sql

   一般先看执行计划和相关字段是否建了索引

 

3.字段建索引

-- 图片表建索引
ALTER TABLE pictures ADD INDEX IDX_TOPIC_ID ( `topic_id`, `user_id`,`comment_id`);

 

4.mysql通过update更新子条件不能用in,这点与Oracle有区别。mysql的写法如下：

update  users a inner join users u on  a.id=u.id set a.verify_status=0,a.verify_src=2  where u.verify_status=1 and u.verify_src=1;

 

 5.多字段排序，先根据具体某个数量排序，再根据时间排序。

方法一：

SELECT
        ua.addtime,u.city, u.picture_count,u.hiding_distance,u.gender,u.user_id,u.nick_name,u.avatar,
        u.age,u.is_vip,u.vip_end_time, u.dating_range, u.job,u.longitude,u.latitude,
        l.addtime last_login_time,u.is_authenticated, u.last_online_time AS lastOnlineTime
        FROM `user` u LEFT JOIN login_leg l ON l.user_id = u.user_id
        JOIN (SELECT addtime,user_id from user_attestation WHERE user_id !='1') ua ON ua.user_id = u.user_id
        WHERE u.user_id !='1'
        AND u.gender=0
        AND u.hiding_all_info = 0
        AND is_finish_info = 1
 ORDER BY IF(u.picture_count = 12,1,0)desc,ua.addtime Desc

 方法二：

添加一个标识字段，如下：

UPDATE `user` SET picture_status=1 WHERE picture_count = 12;
ALTER TABLE `user` ADD picture_status INT(11) DEFAULT 0 COMMENT '照片12张状态为1';

 

SELECT
        ua.addtime,u.city, u.picture_count,u.hiding_distance,u.gender,u.user_id,u.nick_name,u.avatar,
        u.age,u.is_vip,u.vip_end_time, u.dating_range, u.job,u.longitude,u.latitude,
        l.addtime last_login_time,u.is_authenticated, u.last_online_time AS lastOnlineTime
        FROM `user` u LEFT JOIN login_leg l ON l.user_id = u.user_id
 JOIN user_attestation ua ON(ua.`user_id`=u.`user_id`)
        -- LEFT JOIN (SELECT ADDTIME,user_id FROM user_attestation WHERE ADDTIME IS NOT NULL) ua ON ua.user_id = u.user_id
        WHERE u.user_id !='1'
        AND u.gender=0
        AND u.hiding_all_info = 0
        AND is_finish_info = 1
-- AND u.picture_count != 12
-- and ua.addtime is not null
ORDER BY  u.picture_status DESC , ua.addtime DESC;