mysql存儲過程table

『壹』 mysql 在存儲過程中創建表格

DELIMITER$$
USE`test`$$
DROPPROCEDUREIFEXISTS`p_create_table`$$
CREATEDEFINER=`root`@`localhost`PROCEDURE`p_create_table`()
BEGIN
DECLAREtableNameVARCHAR(100);
DECLAREtable_nameVARCHAR(100);
DECLAREmonthIntINTDEFAULT1;
DECLAREmonthStrVARCHAR(16);
DECLAREtableHeaderVARCHAR(16);
DECLAREtableBodyVARCHAR(255);
DECLAREdropTableStrVARCHAR(255);
DECLAREsqlStrVARCHAR(1000);
SETtableName=CONCAT('test_',DATE_FORMAT(NOW(),'%Y'));
SETtableHeader='CREATETABLE';
SETtableBody='(`id`INT(11)NOTNULLAUTO_INCREMENT,`name`VARCHAR(10),PRIMARYKEY(`id`))ENGINE=INNODBDEFAULTCHARSET=utf8;';
WHILEmonthInt<=12DO
IFmonthInt<10THEN
SETmonthStr=CONCAT('0',monthInt);
ELSE
SETmonthStr=CONCAT(monthInt);
ENDIF;
SETtable_name=CONCAT(tableName,monthStr);
SETdropTableStr=CONCAT('DROPTABLEIFEXISTS',table_name);
SETsqlStr=CONCAT(tableHeader,table_name,tableBody);
SELECTdropTableStrINTO@dropTableStr;
SELECTsqlStrINTO@sqlStr;
BEGIN
PREPAREstepDropExistTableFROM@dropTableStr;
EXECUTEstepDropExistTable;
PREPAREstepCreateTbaleFROM@sqlStr;
EXECUTEstepCreateTbale;
END;
SETmonthInt=monthInt+1;
ENDWHILE;
END$$
DELIMITER;

『貳』 mysql在存儲過程中先判斷資料庫中是否存在table1表,有就刪除，沒有就新建

是的 這個主要是在增刪改查的時候用到
if TABLE1 EXISTING DROP TABLE1;
CREATE TABLE

『叄』 為什麼在mysql存儲過程中創建的臨時表不能

提到MySQL臨時表，我們都很熟悉了，一般來說，分為兩類：

1. MySQL 臨時表引擎，名字叫做 Memory。比如

create table tmp1(id int, str1 varchar(100) ) engine = memory;

由參數max_heap_table_size 來控制，超過報錯。

2. 非臨時表的引擎，這里又分為兩類：

• 用戶自定義的臨時表，比如:

• create temporary table (id int, str1 varchar(100) );



• SQL執行過程中產生的內部臨時表，比如：UNION , 聚合類ORDER BY，派生表，大對象欄位的查詢，子查詢或者半連接的固化等等場景。

那麼這兩種臨時表的計數器通常用show global status like '%tmp_%tables%' 來查看。比如

• mysql> show status like '%tmp_%tables%';``+-------------------------+-------+``| Variable_name | Value |``+-------------------------+-------+``| Created_tmp_disk_tables | 0 |``| Created_tmp_tables | 0 |``+-------------------------+-------+``2 rows in set (0.00 sec)



以上結果分別代表，只創建磁碟上的臨時表計數以及臨時表的總計數。這兩個計數器由參數 tmp_table_size 和 max_heap_table_size 兩個取最小值來控制。

那在 MySQL 5.7 之前，這個 SQL 運行中產生的臨時表是 MYISAM，而且只能是 MYISAM。那 MySQL 從 5.7 開始提供了參數 Internal_tmp_mem_storage_engine 來定義內部的臨時表引擎，可選值為 MYISAM 和 INNODB 。當然這里我們選擇 INNODB 。並且把內部的臨時表默認保存在臨時表空間 ibtmp1 (可以用參數 innodb_temp_data_file_path 設置大小以及步長等)下。當然這里我們得控制下 ibtmp1 的大小，要不然一個爛SQL就把磁碟整爆了。

但是MySQL 5.7 之前都沒有解決如下問題:

• VARCHAR的變長存儲。那就是如果臨時表的欄位定義是 VARCHAR(200)，那麼映射到內存里處理的欄位變為CHAR(200)。假設 VARCHAR(200) 就存里一個字元 "Y", 那豈不是很大的浪費。

• 大對象的默認磁碟存儲，比如 TEXT，BLOB， JSON等，不管裡面存放了啥，直接轉化為磁碟存儲。

MySQL 8.0 開始，專門實現了一個臨時表的引擎 TempTable , 解決了 VARCHAR欄位的邊長存儲以及大對象的內存存儲。由變數 interal_tmp_mem_storage_engine來控制，可選值為 TempTable（默認）和 Memory；新引擎的大小由參數temp_table_max_ram 來控制，默認為1G。超過了則存儲在磁碟上（ibtmp1）。並且計數器由性能字典的表 memory_summary_global_by_event_name 來存儲。

• mysql> SELECT * FROM performance_schema. memory_summary_global_by_event_name WHERE event_name like '%temptable%'G*************************** 1. row *************************** EVENT_NAME: **memory/temptable/physical_disk** COUNT_ALLOC: 0 COUNT_FREE: 0 SUM_NUMBER_OF_BYTES_ALLOC: 0 SUM_NUMBER_OF_BYTES_FREE: 0 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 0 HIGH_COUNT_USED: 0 LOW_NUMBER_OF_BYTES_USED: 0CURRENT_NUMBER_OF_BYTES_USED: 0 HIGH_NUMBER_OF_BYTES_USED: 0*************************** 2. row *************************** EVENT_NAME: **memory/temptable/physical_ram** COUNT_ALLOC: 1 COUNT_FREE: 0 SUM_NUMBER_OF_BYTES_ALLOC: 1048576 SUM_NUMBER_OF_BYTES_FREE: 0 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 1 HIGH_COUNT_USED: 1 LOW_NUMBER_OF_BYTES_USED: 0CURRENT_NUMBER_OF_BYTES_USED: 1048576 HIGH_NUMBER_OF_BYTES_USED: 10485762 rows in set (0.03 sec)



以上 memory/temptable/physical_disk 代表放入磁碟上的臨時表計數情況。

memory/temptable/physical_ram 代表放入內存的臨時表計數情況。

那總結下MySQL 8.0 引入的 TempTable 引擎：

• 默認內部臨時表引擎。

• 支持變長字元類型的實際存儲。

• 設置變數 temp_table_max_ram 來控制實際存儲內存區域大小。

『肆』 mysql使用存儲過程實現多個資料庫創建同一個表

二個關鍵：
第一，你的訪問用戶，是否對此
二表
有相同的許可權，若是，剛一切皆很簡單。
第二，你在描述其中的某個表時，只需加一個資料庫名即可。即select
wc.table.name
from
wc.table,wcw.table2.name

『伍』 如何創建MySQL存儲過程可以返回一個表類型的數據

首先需要知道「另一個存儲過程」的結果集的所有列的類型。
假設「另一個存儲過程」的名字是sp1，沒有參數，返回的結果集共3列，全部為int型，那麼「存儲過程」里添加一個與結果集列數相同的臨時表或表變數用於接收「另一個存儲過程」的結果集
如下
CREATE PROCEDURE sp2
AS
DECLARE @t table(a int,b int,c int)

INSERT INTO @t(a,b,c)
EXEC sp1

SELECT * FROM @t
使用SQLSERVER存儲過程可以很大的提高程序運行速度，簡化編程維護難度，現已得到廣泛應用。
創建存儲過程
和數據表一樣，在使用之前需要創建存儲過程，它的簡明語法是：

引用:
Create PROC 存儲過程名稱
[參數列表（多個以「,」分隔）]
AS
SQL 語句

例：

引用:
Create PROC upGetUserName
@intUserId INT,
@ostrUserName NVARCHAR(20) OUTPUT -- 要輸出的參數
AS
BEGIN
-- 將uName的值賦給 @ostrUserName 變數，即要輸出的參數
Select @ostrUserName=uName FROM uUser Where uId=@intUserId
END

其中 Create PROC 語句（完整語句為Create PROCEDURE）的意思就是告訴SQL SERVER，現在需要建立咐漏一個存儲過程，upGetUserName 就是存儲過程名稱，@intUserId 和 @ostrUserName 分別是該存儲過程的兩個參數，注意，在SQL SERVER中，所有用戶定義的變數都以「@」開頭，OUTPUT關鍵字表示這個參數是用來輸出的，AS之後就是存儲過程內容了。只要將以上代碼在「查詢分析器」掘尺里執行一次，SQL SERVER就會在當前資料庫中創建一個名為「upGetUserName」的存儲過程。你可以打開「企業管理器」，選擇當前操作的資料庫，然後在左邊的樹型列表中選擇「存儲過程」，此時就可以在右邊的列表中看到你剛剛創建的存儲過程了（如果沒有，刷新一下即可）。
二、存儲過程的調用

之前已經創建了一個名為「upGetUserName」的存儲過程，從字面理解該存儲過程的功能是用來取得某一個用戶的名稱。存儲過程建立好了，接下來就是要在應用程序里調用了，下面看一下在ASP程序里的調用。

引用:
Dim adoComm
』// 創建一個對象，我們用來調用存儲過程
Set adoComm = CreateObject("ADODB.Command")
With adoComm
』// 設置連接，設 adoConn 為已經連接的 ADODB.Connection 對象
.ActiveConnection = adoConn
』// 類型為存儲過程，adCmdStoredProc = 4
.CommandType = 4
』// 存儲過程名稱
.CommandText = "upGetUserName"
』// 設置用戶編號
.Parameters.Item("@intUserId").Value = 1
』// 執行存儲過程
.Execute

』// 取得從存儲過程返回的用戶名稱
Response.Write "用戶名：" & .Parameters.Item("@ostrUserName").Value
End With
』// 釋放對象
Set adoComm = Nothing

通過以上兩步，已經可以創建和使用簡單的存儲過程了。下面來看一個稍微復雜點的存儲過程，以進一步了解存儲過程的應用。
三、存儲過程的實際應用

用戶登錄在ASP項目中經常會使用到，但使用存儲過程來做驗證可能不多，那麼做例子，寫一個簡單的用戶登錄驗證衡散爛的存儲過程。

引用:
Create PROC upUserLogin
@strLoginName NVARCHAR(20),
@strLoginPwd NVARCHAR(20),
@blnReturn BIT OUTPUT
AS
-- 定義一個臨時用來保存密碼的變數
DECLARE @strPwd NVARCHAR(20)
BEGIN
-- 從表中查詢當前用戶的密碼，賦值給 @strPwd 變數，下面要對他進行比較
Select @strPwd=uLoginPwd FROM uUser Where uLoginName=@strLoginName

IF @strLoginPwd = @strPwd
BEGIN
SET @blnReturn = 1
-- 更新用戶最後登錄時間
Update uUser SET uLastLogin=GETDATE() Where uLoginName=@strLoginName
END
ELSE
SET @blnReturn = 0
END

用戶登錄的存儲過程建立好了。注意，在一個區域內如果有多條語句時，必需使用BEGIN...END關鍵字。
引用:
Dim adoComm
』// 創建一個對象，我們用來調用存儲過程
Set adoComm = CreateObject("ADODB.Command")
With adoComm
』// 設置連接，設 adoConn 為已經連接的 ADODB.Connection 對象
.ActiveConnection = adoConn
』// 類型為存儲過程，adCmdStoredProc = 4
.CommandType = 4
』// 存儲過程名稱
.CommandText = "upUserLogin"
』// 設置登錄名稱
.Parameters.Item("@strLoginName").Value = "***"
』// 設置登錄密碼
.Parameters.Item("@strLoginPwd").Value = "123456"
』// 執行存儲過程
.Execute

』// 判斷是否登錄成功
If .Parameters.Item("@blnReturn").Value = 1 Then
Response.Write "恭喜你，登錄成功！"
Else
Response.Write "不是吧，好像錯了哦。。。"
End If
End With
』// 釋放對象
Set adoComm = Nothing

通過以上的步驟，簡單用戶登錄驗證過程也做完了，現在只要把它整合到程序中就可以實現簡單的用戶登錄驗證了，關於其他細節就由你自己來處理了。
上面介紹的兩個存儲過程都是只返回一個值的，下面我們來看一個返回一個記錄集的存儲過程。

引用:
Create PROC upGetUserInfos
@intUserGroup INT
AS
BEGIN
-- 從資料庫中抽取符合條件的數據
Select uName,uGroup,uLastLogin FROM uUser Where uGroup=@intUserGroup
-- 插入一列合計
UNION
Select 』合計人數:』,COUNT(uGroup),NULL FROM uUser Where uGroup=@intUserGroup
END

現在我們來看一下ASP程序的調用。

引用:
Dim adoComm
Dim adoRt
』// 創建一個對象，我們用來調用存儲過程
Set adoComm = CreateObject("ADODB.Command")
Set adoRs = CreateObject("ADODB.Recordset")
With adoComm
』// 設置連接，設 adoConn 為已經連接的 ADODB.Connection 對象
.ActiveConnection = adoConn
』// 類型為存儲過程，adCmdStoredProc = 4
.CommandType = 4
』// 存儲過程名稱
.CommandText = "upGetUserInfos"
』// 設置用戶組
.Parameters.Item("@intUserGroup").Value = 1
』// 執行存儲過程，和以上幾個例子不同，這里使用RecordSet的Open方法
adoRs.Open adoComm
』// 顯示第一個值
Response.write adoRs.Fields(0).Value
End With
』// 釋放對象
Set adoRs = Nothing
Set adoComm = Nothing

『陸』 mysql存儲過程支持用表作為參數嗎

mysql存儲過程支持用表作為參數。

這個問題在MySQL 5.0以前非常麻煩，但是在MySQL 5.0.13版之後，由於引入了PREPARE語句，一切變得簡單了。
例子如下（已驗證）：

DROPPROCEDUREIFEXISTS`newtable`;
CREATEPROCEDURE`newtable`(INtnamevarchar(64))
BEGIN
SET@sqlcmd=CONCAT('CREATETABLE',tname,'(idintNOTNULLAUTO_INCREMENT,namevarchar(64)DEFAULTNULL,PRIMARYKEY(`id`))');
PREPAREstmtFROM@sqlcmd;
EXECUTEstmt;
DEALLOCATEPREPAREstmt;
END;
callnewtable('abc');