Mysql 问题汇总

一、mysql 语句分析与优化
详解
二、自动更新创建时间和更新时间
自动创建时间 NOT NULL DEFAULT CURRENT_TIMESTAMP
自动创建时间和自动更新 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

  CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建时会自动生成创建时间create_time和更新时间update_time

INSERT INTO test (name) VALUES ('aa'),('bb'),('cc');

更新数据时会自动更新update_time

UPDATE test SET name = 'ab' WHERE id = 1;

三、查询
行转列 GROUP_CONCAT

select  reg_source,count(id) from (
 select  reg_source,user.uuid as id
  from user
  left join device_info on  user.uuid=device_info.`user_id` AND grant_by=0
  left join product on  product.code=device_info.`product_code`
  where
  user.`country`="US" 
 group by reg_source,user.uuid
 having ( GROUP_CONCAT(appliance,',') not like"%Cleaning%" or GROUP_CONCAT(appliance,',')  is null)
)   tmp
group by reg_source

spark sql

select  reg_source,count(id) from (
  select  reg_source,user.uuid as id ,concat_ws(',',collect_list(appliance)) as   appliance_total
   from user
   left join device_info on  user.uuid=device_info.`user_id` AND grant_by=0
   left join product on  product.code=device_info.`product_code`
   where
   user.`country`="US"
  group by reg_source,user.uuid
  having (concat_ws(',',collect_list(appliance)) not like"%Cleaning%" or concat_ws(',',collect_list(appliance))  is null)
)  tmp
group by reg_source

四、自增长key的0值
主键是自增长时，如果插入语句的key是0或者null，mysql会自动生成一个key
自增长和uuid是优缺点以及使用场景

五、mysql8可视化工具连接问题
Authentication plugin ‘caching_sha2_password’ cannot be loaded
原因
不是客户端的原因，是MySQL兼容问题，需要修改数据库的认证方式
MySQL8.0版本默认的认证方式是caching_sha2_password
MySQL5.7版本则为mysql_native_password。

解决办法
打开系统偏好设置，找到mysql，点击Initialize Database。
输入你的密码。
选择‘Use legacy password‘。
重启mysql服务。
重新使用工具链接数据库