006-mysql 一些小技巧

mysql 一些小技巧

1、字符串连接

1）concat(str1,str2,…)：返回结果为连接参数产生的字符串。
     注意：如果其中一个参数为 null，则整个返回结果为 null。

SELECT CONCAT(‘tom’,‘cat’) as name
结果：tomcat
SELECT CONCAT(‘tom’,‘cat’,null) as name
结果：null

2）concat_ws(separator,str1,str2,…)：第一个参数是分隔符，返回结果是用分隔符分隔连接所有参数产生的字符串。
     注意：如果分隔符为 null，则整个返回结果为 null，其他参数为 null 时，结果中会忽略该参数。

》SELECT CONCAT_ws(’=’,‘tom’,‘cat’) as name
结果：tom=cat
》SELECT CONCAT_ws(’’,‘tom’,‘cat’) as name
结果：tomcat
》SELECT CONCAT_ws(null,‘tom’,‘cat’) as name
结果：null
》SELECT CONCAT_ws(’=’,‘tom’,null,‘kitty’) as name
结果：tom=kitty

3）group_concat(col,separator)：按分组连接字段值。默认按逗号分隔。

SELECT name,group_concat(id) ids FROM user group by name;
SELECT name,group_concat(id separator ‘;’) ids FROM user group by name;

2、length 和 char_length

length(col)：字段的大小（字节数）。
char_length(col)：字段值的长度（字符个数）。
————————————————————————
对于 oracle
lengthb(string)：计算string所占的字节长度；返回字符串的长度，单位是字节
length(string)：计算string所占的字符长度；返回字符串的长度，单位是字符
注：
对于单字节字符，LENGTHB 和 LENGTH 是一样的。所以可以用length(‘string’)=lengthb(‘string’)判断字符串是否含有中文。
一个汉字在Oracle数据库里占多少字节跟数据库的字符集有关，UTF８时，长度为三。
select lengthb(‘人’) from dual ;可查询汉字在 Oracle 数据库里占多少字节
————————————————————————
对于 postgresql
https://yq.aliyun.com/articles/241195
length()、char_length()：计算字符串字符长度
select ' 人 ' as name,length(' 人 ') as len from dual ;
select ' 人 ' as name,char_length(' 人 ') as len from dual ;
octet_length()：计算字符串大小
select ' 人 ' as name,octet_length(' 人 ') as len from dual ;

3、select 直接打印

select ‘tomcat’;
select ‘tomcat’ as name;
SELECT now();

4、mysql 中的加号（+）

mysql 中的加号只有一个作用，就是算术运算符。
1）加号运算符执行前，会试图将不是数值的转换成数值，如果不能直接转换，则被转换的对象直接使用 0 代替。
如：select ‘tom’ + 3;
结果为：3
2）如果加号连接的一方为 null，则整个运算的结果也为 null。
如：select null + 5;
结果为：null

5、escape 转义

在 like 语句中，%（任意个字符） 和 _（单个字符）是通配符。
下面有这样一个场景：
如果在查询字段中包含 % 或 _，而查询条件需要使用 % 或 _ 去作为匹配条件，而不是作为通配符使用，则这时需要将 % 和 _ 进行转义。

# 法一：使用斜杠 \ 进行转义
select name from user where like '\%kit%';
# 结果为：%kitty

# 法二：使用 escape 指定转义符
select name from user where like '$%kit%' escape '$';
# 这里 $ 就作为转义（可以为任何字符，不仅仅是$，如 a）。
select name from user where like 'a%kit%' escape 'a';

6、between … and …

包含边界值。

7、索引起始值

所有 sql 中，索引值都是从 1 开始的。

8、数据导入导出

命令导出
mysqldump -uroot -p123456 dbname tablename > test.sql
命令导入
mysql -uroot -p123456 dbname < test.sql
【xtrabackup 性能更高】

9、sql 优化

定位：
1）查看日志（通过 show variables like ‘%slow%’ 找到日志）
2）explain sql语句
3）profile
【注意】
测试 sql 速度，应先关闭 sql 缓存。