维护mysql 和postgres 的时候会踩到很多坑,这里总结一些,一面后面再遇到。
如果不习惯CSDN的markdown排版可以点这里,这篇内容的markdown支持真是差,后续的更新可能不一定会及时同步到博客。个人笔记版
执行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;
[x] 如果已经存在test就丢弃掉,不然第二次创建函数的时候会报重名的错误。
DELIMITER $$
[x] 定义代码片分隔符,mysql默认见到分号就执行了,这里设置为$$表明见到$$就执行,可以理解问分号。后面可以看到执行完马上又复原了。这个分隔符你想怎么取就怎么取,只要不和其他的冲突就行,比如官方文档里经常会取//。
returns varchar(100) NONDETERMINISTIC
[x] 这里可以多了一个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
[x] begin和end组成了一个代码片,你可以在里面执行函数体的代码,比如这里的函数就在begin和end里。只需要注意别把return之类的句子写到end后面就行了。
[ ] 如果函数体代码不是直接返回,就需要加一个begin…end包一下。
[x] 后面的就是基本语法了,这里写的三个是比较常用的,两个循环一个条件判断,实际用的过程中只需要照葫芦画瓢就行了,人家非要这么写,你也没办法。
基本常用的函数就照这个这个复杂的例子改改就可以了,不过需要注意的是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分钟的时间, 如果不做随机,速度会更快。