mysql存储过程创建表

A. 为什么在mysql存储过程中创建的临时表不能自动销毁

临时表

可以创建本地临时表和全局临时表。本地临时表仅在当前会话中可见，而全局临时表在所有会话中都可见。临时表不能分区。

本地临时表的名称前面有一个数字符号 (#table_name)，而全局临时表的名称前面有两个数字符号
(##table_name)。

除非使用 DROP TABLE 显式删除临时表，否则临时表将在退出其作用域时由系统自动删除：

当存储过程完成时，将自动删除在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表。

所有其他本地临时表在当前会话结束时都将被自动删除。

B. 为什么在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 来控制实际存储内存区域大小。

C. mysql存储过程输入一个变量新建一个该变量的列表名

一般没有这种做法的, 列表名或者说字段名都是在设计表的时候固定设计好了, 如果动态添加的话指不定什么时候表就因为字段太多出现什么不可预测的问题了来
不过一般你可以变相使用行做动态数据, 例如设计一个表, 字段有id(自增), field_name(存储列表名), filed_value(存储列表值), 达成同样的目的
到时候只要插入相应的行数据就可以了, 而不必改变表结构

D. 在MySQL中如何创建一个带输出参数为一个表类型的存储过程

在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程序里的调用。

E. 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;

F. 如何创建MySQL存储过程可以返回一个表类型的数据

如何创建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程序里的调用。

G. 怎么用mysql的存储过程创建表， 不要用这种方法：PREPARE DD FROM @sqlstr; EXECUTE DD; 能不能直接运行@s

如果你的表明是固定的 那么可以用CREATE TABLE直接创建，如果表明是动态的，那只有用PREPARE 了， 没办法

H. mysql使用存储过程实现多个数据库创建同一个表

二个关键：
第一，你的访问用户，是否对此
二表
有相同的权限，若是，刚一切皆很简单。
第二，你在描述其中的某个表时，只需加一个数据库名即可。即select
wc.table.name
from
wc.table,wcw.table2.name

I. mysql如何创建存储过程

1 用mysql客户端登入
2 选择数据库
mysql>use test
3 查询当前数据库有哪些存储过程
mysql>show procere status where Db='test'
4 创建一个简单的存储过程
mysql>create procere hi() select 'hello';
5 存储过程创建完毕,看怎么调用它
mysql>call hi();
显示结果 mysql> call hi();
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
6 一个简单的储存过程就成功了

J. mysql 创建存储过程。

/*先建表stugra*/
CREATE TABLE `stugra` (
`name` CHAR(20) DEFAULT NULL,
`mark` INT(3) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=gbk

INSERT INTO stugra VALUES ('aa','59');
INSERT INTO stugra VALUES ('fa','11');
INSERT INTO stugra VALUES ('aga','99');
INSERT INTO stugra VALUES ('v','32');
INSERT INTO stugra VALUES ('da','77');
INSERT INTO stugra VALUES ('gf','44');
INSERT INTO stugra VALUES ('sadf','0');
INSERT INTO stugra VALUES ('eef','32');
INSERT INTO stugra VALUES ('ggg','60');
存储过程：
DELIMITER $$
DROP PROCEDURE IF EXISTS `pro_find`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_find`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a,b INT DEFAULT 0;

DECLARE cur1 CURSOR FOR SELECT mark FROM stugra;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a;
IF NOT done THEN
IF a<60 THEN
SET b=b+1;
END IF;
ELSE
SELECT b;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END$$

DELIMITER ;

没使用count ，使用了循环和选择，还使用了游标，没说不能使用游标吧！