MySQL一次性創建表格存儲過程實戰

Posted on by

一、創建表格

創建下個月的每天對應的表user_2022_01_01、…

需求描述：

我們需要用某個表記錄很多數據，比如記錄某某用戶的搜索、購買行為(註意，此處是假設用數據庫保存)，當每天記錄較多時，如果把所有數據都記錄到一張表中太龐大，需要分表，我們的要求是，每天一張表，存當天的統計數據，就要求提前生產這些表——每月月底創建下一個月每天的表！

PREPARE stmt_name FROM preparable_stmt
EXECUTE stmt_name [USING @var_name [, @var_name] ...]
{DEALLOCATE | DROP} PREPARE stmt_name
-- 知識點 時間的處理
-- EXTRACT(unit FROM date)截取時間的指定位置值
-- DATE_ADD(date,INTERVAL expr unit) 日期運算
-- LAST_DAY(date) 獲取日期的最後一天
-- YEAR(date) 返回日期中的年
-- MONTH(date) 返回日期的月
-- DAYOFMONTH(date) 返回日

思路：構建循環語句，創建單個表格比較的簡單，但是對於很多種表格，而且是下個月的表格，對於表命名有一定的要求，所以就需要用到我們之前的日期函數，和字符串函數的一些知識。

-- 思路：循環構建表名 user_2021_11_01 到 user_2020_11_30；並執行create語句。
use mysql7_procedure;
drop procedure if exists proc22_demo;
delimiter $$
create procedure proc22_demo()
begin
declare next_year int;
declare next_month int;
declare next_month_day int;
declare next_month_str char(2);
declare next_month_day_str char(2);
-- 處理每天的表名
declare table_name_str char(10);
declare t_index int default 1;
-- declare create_table_sql varchar(200);

首先利用declare 定義需要的一些變量，next_year（下一年），next_month（下一個月），next_month_day（天數），這裡為什麼要這樣去定義，特別是年，月，不應該是提前知道的嗎？答案是有時候比如是12月呢，那麼下一個月的年份就不一樣瞭，所以需要利用日期函數的一些運算去解決這些問題。

-- 獲取下個月的年份
set next_year = year(date_add(now(),INTERVAL 1 month));
-- 獲取下個月是幾月
set next_month = month(date_add(now(),INTERVAL 1 month));
-- 下個月最後一天是幾號
set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
if next_month < 10
then set next_month_str = concat('0',next_month);
else
set next_month_str = concat('',next_month);
end if;
while t_index <= next_month_day do
if (t_index < 10)
then set next_month_day_str = concat('0',t_index);
else
set next_month_day_str = concat('',t_index);
end if;

上面都是對表的名字的一些字段和別名進行獲取和拼接

set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
-- 拼接create sql語句
set @create_table_sql = concat(
'create table user_',
table_name_str,
'(`uid` INT ,`ename` varchar(50) ,`information` varchar(50)) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
-- FROM後面不能使用局部變量！
prepare create_table_stmt FROM @create_table_sql;
execute create_table_stmt;
DEALLOCATE prepare create_table_stmt;
set t_index = t_index + 1;
end while;
end $$
delimiter ;
call proc22_demo();

這樣就實現瞭效果

二、補充：MySQL的存儲函數與存儲過程的區別

MySQL存儲函數（自定義函數），函數一般用於計算和返回一個值，可以將經常需要使用的計算或功能寫成一個函數。

存儲函數和存儲過程一樣，都是在數據庫中定義一些 SQL 語句的集合。

存儲函數與存儲過程的區別;

  • 1.存儲函數有且隻有一個返回值，而存儲過程可以有多個返回值，也可以沒有返回值。
  • 2.存儲函數隻能有輸入參數，而且不能帶in, 而存儲過程可以有多個in,out,inout參數。
  • 3.存儲過程中的語句功能更強大，存儲過程可以實現很復雜的業務邏輯，而函數有很多限制，如不能在函數中使用insert,update,delete,create等語句；
  • 4.存儲函數隻完成查詢的工作，可接受輸入參數並返回一個結果，也就是函數實現的功能針對性比較強。
  • 5.存儲過程可以調用存儲函數、但函數不能調用存儲過程。
  • 6.存儲過程一般是作為一個獨立的部分來執行(call調用)。而函數可以作為查詢語句的一個部分來調用.
create function func_name ([param_name type[,...]])
returns type
[characteristic ...] 
begin
    routine_body
end;

參數說明：

  • （1）func_name ：存儲函數的名稱。
  • （2）param_name type：可選項，指定存儲函數的參數。type參數用於指定存儲函數的參數類型，該類型可以是MySQL數據庫中所有支持的類型。
  • （3）RETURNS type：指定返回值的類型。
  • （4）characteristic：可選項，指定存儲函數的特性。
  • （5）routine_body：SQL代碼內容。
create database mydb9_function;
-- 導入測試數據
use mydb9_function;
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的創建者
-- 創建存儲函數-沒有輸輸入參數
drop function if exists myfunc1_emp;

delimiter $$
create function myfunc1_emp() returns int
begin
  declare cnt int default 0;
    select count(*) into  cnt from emp;
  return cnt;
end $$
delimiter ;
-- 調用存儲函數
select myfunc1_emp();
create database mydb9_function;
-- 導入測試數據
use mydb9_function;
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的創建者
-- 創建存儲函數-沒有輸輸入參數
drop function if exists myfunc1_emp;

delimiter $$
create function myfunc1_emp() returns int
begin
  declare cnt int default 0;
    select count(*) into  cnt from emp;
  return cnt;
end $$
delimiter ;
-- 調用存儲函數
select myfunc1_emp();

到此這篇關於MySQL一次性創建表格存儲過程實戰的文章就介紹到這瞭,更多相關MySQL創建表格內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：