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

2022-07-11 22:05:03

一、建立表格

建立下個月的每天對應的表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建立表格內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

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

目錄

一、建立表格

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

熱門文章