MYSQL常用技巧

mysql 智课网

维护mysql 和postgres 的时候会踩到很多坑，这里总结一些，一面后面再遇到。
如果不习惯简书的markdown排版可以点这里，后续的更新可能不一定会及时同步到简书。个人笔记版

初始化

• 查看数据库文件存放路径：show variables like 'datadir%'
• docker启动说明。简明教程
• 用户登录设置。链接
• 中文乱码，需要设置database的编码格式为utf-8。

CREATE  DATABASE  mydbname  CHARACTER SET  utf8;
ALTER DATABASE SET CHARACTER utf8;

执行sql文件

sql脚本可以通过下面两种方式执行：

-- 直接连接执行
mysql --host shanyy.me --user user --database test < test-func.sql
-- source 执行
mysql > source some.sql

mycli执行sql的时候好像老是会报错，不知道为啥，可能是我配置的不对吧，如果遇到同样的情况换mysql自带的就好了。

mysql的函数(function)和过程(procedure)

在维护mysql和procedure中总会遇到需要有些无法通过简单的sql完成的需求，一般会想到sql脚本完成。这时候就会遇到sql函数和过程。函数和过程很像，在官方文档里也是放在一起讲的。mysql官方文档——函数和过程
函数
先说函数，函数相对比较简单，而且限制也多一些。下面是一个官方文档抄过来的例子：

-- 定义函数名和输入参数
mysql> CREATE FUNCTION hello (s CHAR(20))
-- 定义返回参数类型
mysql> RETURNS CHAR(50)
-- 函数代码段
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
-- 可以直接在sql语句中执行函数
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

函数的执行过程和内置函数一模一样，你就把它理解为内置函数就可以了，可以直接在sql中用。

定义一个函数很简单，复杂的函数也就是函数体可能复杂点，下面是一个稍微复杂点的，实际上这个函数屁用没有，就是为了展示几个常见问题：

drop function if exists test;
DELIMITER $$ 
create function test()
returns varchar(100) NONDETERMINISTIC
begin
declare size int;
declare k int;
declare s datetime;
declare m float;
declare x double;
declare c1 int;
set size = 1000;
set k = 0;
while k < size do
select cast(count(*) as char) into c1  from draft;
set k = k + 1;
end while;
repeat
select cast(count(*) as char) into c1 from draft;
set k = k - 1;
until k > 0
end repeat;
if c1 > 1000 then
set c1 = 999;
else 
set c1 = 1001;
end if;
return c1;
end $$ 
DELIMITER ;

为了大家好复制，就不在代码片里直接注释了，下面来逐行分析。

drop function if exists test;

• 如果已经存在test就丢弃掉，不然第二次创建函数的时候会报重名的错误。

DELIMITER $$ 

• 定义代码片分隔符，mysql默认见到分号就执行了，这里设置为$$表明见到$$就执行，可以理解问分号。后面可以看到执行完马上又复原了。这个分隔符你想怎么取就怎么取，只要不和其他的冲突就行，比如官方文档里经常会取//。

returns varchar(100) NONDETERMINISTIC

• 这里可以多了一个NONDETERMINISTIC，这个主要是标识你的函数在给定输入的时候输出是否固定的，类似纯函数什么是纯函数，为了简化分析，后面统一称其为纯函数（可能会不严谨）。
  如果是纯函数，则可以写成DETERMINISTIC，如果不是则可以写成NONDETERMINISTIC。如果不写默认为不是纯函数。按官方文档说的是，如果把纯函数标识为非纯函数，则可能会导致性能下降，或者某些性能优化策略失效；反之，把非纯函数标识为纯函数可能导致结果不对。所以如果不确定是不是纯函数还是缺省比较保险。其实判断是不是纯函数的方法很简单，主要看你函数里是否引入了外部变量，比如查表，随机数啊之类的。上面给出官方的例子就是一个纯函数，后面那个复杂一点的就是一个非纯函数。
  按说只要函数中调用了now或者rand就肯定是非纯函数，标记错了计算结果可能会出错。不过mysql文档说明了，可以认为系统会把随机数和时间戳作为默认参数传到函数中去，所以即便标记成NONDETERMINISTIC也不会出错。不过官方文档只是说如果你只调用一次rand才不会出错。原话如下：
  

  A routine that contains the NOW() function (or its synonyms) or RAND() is nondeterministic, but it might still be replication-safe. For NOW(), the binary log includes the timestamp and replicates correctly. RAND() also replicates correctly as long as it is called only a single time during the execution of a routine. (You can consider the routine execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)

begin
...
end

• begin和end组成了一个代码片，你可以在里面执行函数体的代码，比如这里的函数就在begin和end里。只需要注意别把return之类的句子写到end后面就行了。
• 如果函数体代码不是直接返回，就需要加一个begin...end包一下。

• 后面的就是基本语法了，这里写的三个是比较常用的，两个循环一个条件判断，实际用的过程中只需要照葫芦画瓢就行了，人家非要这么写，你也没办法。

  基本常用的函数就照这个这个复杂的例子改改就可以了，不过需要注意的是declare一定要放在最前面，比如上面你把从c1的declare放到set的后面看着也没啥问题，实际上就是会报错，我也不知道为啥。

有了这些基础，我们开始尝试着做一些完成一个很常见的需求：如何用mysql快速填充一个测试表。虽然利用python或者nodejs填充表格也很简单，但是效率最高的还是直接使用sql来做。
于是乎，我们尝试写出了下面的代码：

-- 实际上这个代码是无法执行的！！！
drop function if exists initProj;
DELIMITER $$
create function initProj()
begin
declare size int;
declare k int;
drop table tmp_table;
create table tmp_table ( id int(11) not null auto_increment primary key,
                        time datetime,
                        rand_data int(10),
                        rand_str varchar(100));
set size = 1000;
set k = 0;
while size > k do
insert into tmp_table (time, rand_data, rand_str) values (now(), 1000000 * rand(), rand_string(100));
set k = k + 1;
end while ;
end $$
DELIMITER ;

这里之所以错误是因为mysql不允许隐式或显示提交，简单理解就是drop table, create table, truncate table之类的操作都没法执行。有时候你需要清理表格可以考虑利用delete from some_table where id > 0;来做，不过不建议这么做，下面介绍的procedure来做更加优雅。

过程
过程和函数很类似，只不过过程的限制没有函数那么多，带来的副作用就是调用过程需要主动地call procedure而不是直接在sql中调用。

-- 调用procedure的例子
-- \G 参数主要是输出的排版更加友好
call exp_procedure() \G;

过程的定义，函数体都和函数是一样的，这里就不具体介绍了，可以简单理解procedure为一坨代码放到一起实现一个功能。下面是利用procedure和function一起实现的初始化表格的需求。

-- 生成随机字符串
DELIMITER $$
drop function if exists rand_string;
CREATE FUNCTION `rand_string`(n INT)
RETURNS varchar(255)
BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END $$
DELIMITER ;
select rand_string(100);
-- 初始化表格
drop procedure if exists initProj;
DELIMITER $$
create procedure initProj(size int)
begin
declare k int;
drop table tmp_table;
create table tmp_table ( id int(11) not null auto_increment primary key,
                        time datetime,
                        rand_data int(10),
                        rand_str varchar(100));
set k = 0;
while size > k do
    insert into tmp_table (time, rand_data, rand_str) values (now(), 1000000 * rand(), rand_string(100));
    set k = k + 1;
end while ;
end $$
DELIMITER ;

只需要call initProj(100000);即可完成初始化。

其中随机字符串为了图简单没有自己写，网上一搜一大堆，我找了一个拿过来用了，丢掉了一些我不太关心的内容。比如权限之类的，有兴趣可以自己去看，这里吧连接放出来了。随机字符串博客链接

最后初始化十万个数据花费了5分钟的时间， 如果不做随机，速度会更快。

更快地生成测试数据

偶然看到了阿里的慢sql优化的新闻，看了生成测试数据的sql，发现insert by select速度要远远快于一个一个地插入，还没研究为啥，先做个笔记，这里先把sql贴出来，仅供参考。

create table a (id int auto_increment,seller_id bigint,seller_name  varchar(100)  collate utf8_bin ,gmt_create varchar(30),primary key(id));  
insert into a (seller_id,seller_name,gmt_create) values (100000,'uniqla','2017-01-01');
insert into a (seller_id,seller_name,gmt_create) values (100001,'uniqlb','2017-02-01');
insert into a (seller_id,seller_name,gmt_create) values (100002,'uniqlc','2017-03-01');
insert into a (seller_id,seller_name,gmt_create) values (100003,'uniqld','2017-04-01');
insert into a (seller_id,seller_name,gmt_create) values (100004,'uniqle','2017-05-01');
insert into a (seller_id,seller_name,gmt_create) values (100005,'uniqlf','2017-06-01');
insert into a (seller_id,seller_name,gmt_create) values (100006,'uniqlg','2017-07-01');
insert into a (seller_id,seller_name,gmt_create) values (100007,'uniqlh','2017-08-01');
insert into a (seller_id,seller_name,gmt_create) values (100008,'uniqli','2017-09-01');
insert into a (seller_id,seller_name,gmt_create) values (100009,'uniqlj','2017-10-01');
insert into a (seller_id,seller_name,gmt_create) values (100010,'uniqlk','2017-11-01');
insert into a (seller_id,seller_name,gmt_create) values (100011,'uniqll','2017-12-01');
insert into a (seller_id,seller_name,gmt_create) values (100012,'uniqlm','2018-01-01');
insert into a (seller_id,seller_name,gmt_create) values (100013,'uniqln','2018-02-01');
insert into a (seller_id,seller_name,gmt_create) values (100014,'uniqlo','2018-03-01');
insert into a (seller_id,seller_name,gmt_create) values (100015,'uniqlp','2018-04-01');
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) select  seller_id,seller_name,gmt_create  from a;
insert into a (seller_id,seller_name,gmt_create) values (100016,'uniqlq',now());