Tag: mysql

mysql数据表设计经验（结合laravel）

这是一份对前阶段数据表设计经验的总结：

READ MORE

mysql存储过程

sql存储过程的实现跟一般的语言相比，有点特殊，有很多值得注意的地方，下面以一个支付的存储过程为例：

READ MORE

使用laravel封装mysql存储过程的调用接口

在使用存储过程时，调用时通常如下：

1
2
3
set @out_status = 0;
call procedure_name(param1, ...,, @out_status);
select @out_status;

比较啰嗦，有必要进行一次封装，如下：

READ MORE

mysql中的TIME\_FORMAT与DATE\_FORMAT函数

之前一直使用TIME_FORMAT来格式化日期，如下：

1
select TIME_FORMAT(CURRENT_TIMESTAMP, '%y%m%d');

但是今天发现在有些版本的mysql上会返回0，不是想要的结果。查了一下：

1
2
3
TIME_FORMAT(time,format)  
这象上面的DATE_FORMAT()函数一样使用，但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。
其他修饰符产生一个NULL值或0。

DATE_FORMAT

改成使用DATE_FORMAT即可：

1
select DATE_FORMAT(CURRENT_TIMESTAMP, '%y%m%d');

mysql存储过程中的select into与局部变量的问题

今天解决了存储过程中的select into与局部变量的问题，原来存储过程大概这样：

1
2
3
4
5
6
7
8
# 在存储过程内部，省略
DECLARE user_uid INT;
SET user_uid = 0;
SELECT `user_uid` INTO user_uid
FROM table
WHERE ...;

结果user_uid的值一直为0，确认数据表满足条件是有一条数据的。试了很多种方法才发现是into赋值的变量不能和select的字段名相同，甚至不能用declare来定义，所以最终解决是这样：

1
2
3
4
5
6
7
8
# 注意不能定义user_uid这个局部变量
DECLARE lc_user_uid INT;
SET lc_user_uid = 0;
SELECT `user_uid` INTO lc_user_uid
FROM table
WHERE ...;

mysql存储过程管理（laravel下）

直接在mysql这个层面（例如命令行或者phpmyadmin等）管理存储过程是一个非常麻烦的事情：

  • 变更很难管理
  • 无法进行版本控制

第一步：把存储过程文件都放到git下管理

例如放在项目的sql目录下：

1
2
3
4
5
6
$ ls sql/
proc_money_appeal_payout.sql             proc_money_apply_cash_payout.sql
proc_money_apply_cash_income_close.sql   proc_money_reward_income.sql
proc_money_apply_cash_income_fail.sql    proc_money_reward_payout.sql
proc_money_apply_cash_income_finish.sql  proc_money_security_deposit_income.sql
proc_money_apply_cash_income.sql         proc_money_security_deposit_payout.sql

第二步：生成导入数据库的sql文件

如果项目的存储过程比较多，每次更新时都可能会变得很麻烦，所以需要统一的导入入口，例如sql目录下生成source.sql文件，内容如下：

1
2
3
4
5
6
7
8
9
10
// vim sql/source.sql
source /home/code/ibbd/yanjiuquan-php/sql/proc_money_appeal_payout.sql;
source /home/code/ibbd/yanjiuquan-php/sql/proc_money_apply_cash_income.sql;
source /home/code/ibbd/yanjiuquan-php/sql/proc_money_apply_cash_income_close.sql;
source /home/code/ibbd/yanjiuquan-php/sql/proc_money_apply_cash_income_fail.sql;
source /home/code/ibbd/yanjiuquan-php/sql/proc_money_apply_cash_income_finish.sql;
source /home/code/ibbd/yanjiuquan-php/sql/proc_money_apply_cash_payout.sql;
source /home/code/ibbd/yanjiuquan-php/sql/proc_money_apply_cash_payout_close.sql;
source /home/code/ibbd/yanjiuquan-php/sql/proc_money_apply_cash_payout_finish.sql;
source /home/code/ibbd/yanjiuquan-php/sql/proc_money_reward_income.sql;

说明：这个source文件不应该加入git版本控制中。

这样只需要在命令行中，source这个文件即可：

1
source /path/to/source.sql

第三步：自动生成source.sql

如果手动管理这个文件，也是挺麻烦的，可以结合laravel自动生成：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
use Illuminate\Database\Seeder;
class ProcdureInitTableSeeder extends Seeder {
    public function run()
    {
        $path  = 'sql/proc_*.sql';
        $root  = getcwd();
        $files = glob($path);
        $source_file = $root . '/sql/source.sql';
        file_put_contents($source_file, "# @desc   导入及更新存储过程结构, 生成命令：php artisan db:seed\n");
        file_put_contents($source_file, "# @author Alex\n", FILE_APPEND);
        file_put_contents($source_file, "# @date   " . date('Y-m-d') . "\n\n", FILE_APPEND);
        foreach ($files as $file) {
            $sql = "source {$root}/{$file};\n";
            file_put_contents($source_file, $sql, FILE_APPEND);
        }
    }
}

The last

每次变化的时候，还是需要手动source一下，略麻烦。。。暂时没有更好的方式。。。