mysql常用语句汇总

根据汉字首字母排序

字段字符编码为utf8_general_ci时
select * from tablename WHERE 1=1 order by CONVERT( fieldname USING gbk ) COLLATE gbk_chinese_ci asc;
对字段重新编码，然后使用COLLATE排序

字段字符编码为latin1_swedish_ci时
select * from tablename order by birary(fieldname ) asc;
binary：将字符串强制转换为二进制字符串

插入时校验重复/防止插入重复数据

插入时检查是否已存在：

insert into a_test (name ,age ,created_at )
select "Tom",26,"2021-08-03 11:28:01"
from dual
where not exists (select 1 from a_test where name = "Tom")

插入时根据主键或唯一索引判断，存在则忽略：

insert ignore into a_test (id ,name ,age ,created_at )
values (1,"Tom", 26, "2021-08-03 11:27:06")

如果存在且数据有一个不一样的，先删后插，影响行数为2

replace into a_test (id ,name ,age ,created_at )
values (1,"Bob", 26, "2021-07-03 11:27:06")

存在则更新，若数据有不一致的，影响行数也是2，注意死锁问题

insert into a_test (id ,name ,age ,created_at )
values (1,"Andy", 28, "2021-09-03 11:27:06") 
on duplicate key update
age = "29", created_at = "2021-11-03 12:01:35"

查询时拼接多个字段

例如有数据：

id	name	age	created_at
8	John	26	[NULL]

使用concat可直接拼接括号中的内容，但是只要一个字段为null，则返回null

select concat(name,":",age,":",id,created_at) 
from a_test at2 
where id = 8

使用concat_ws指定分隔符的链接，有null的字段将跳过
如下得到John:26:8，为null的created_at包括分隔符均跳过
注：concat_ws第一个参数为分隔符

select concat_ws(":",name,age,created_at ,id ) 
from a_test at2 
where id = 8

配合ifnull，如果为null则使用空字符串代替
如下将得到John:26::8，方便根据结果分析那些字段为空

select concat_ws(":",name,age,ifnull(created_at,"") ,id ) 
from a_test at2 
where id = 8

查询包含中文的记录

根据字符集为UTF-8时，中文的length和char_length长度不一致判断

select * from a_test at3 where length(name) != char_length(name)

char_length(str)：计算单位：字符
不管汉字还是数字或者是字母都算是一个字符
length(str)：计算单位：字节
utf8编码：一个汉字三个字节，一个数字或字母一个字节
gbk编码：一个汉字两个字节，一个数字或字母一个字节

时间戳、字符串转换

时间转字符串
select date_format(now(), ‘%Y-%m-%d’);
时间转时间戳
select unix_timestamp(now());
字符串转时间
select str_to_date(‘2021-10-02’, ‘%Y-%m-%d %H’);
字符串转时间戳
select unix_timestamp(‘2021-10-02’);
时间戳转时间
select from_unixtime(1633104000);
时间戳转字符串
select from_unixtime(1633104000,’%Y-%m-%d’);

日期格式定义