在创建数据库和表之前创建过程 [英] Creating a Procedure prior to the creation of database and table

问题描述

我正在尝试在创建数据库的过程中创建一个过程，mysql查询如下:

I am trying to create a Procedure during the creation of a database, the mysql query is as follows:

CREATE DATABASE database_Sensor1;
USE database_Sensor1;
CREATE TABLE Persons(id int NOT NULL);
DELIMITER //
CREATE PROCEDURE MYLOOP() IF database_Sensor1 EXISTS
BEGIN 
      DECLARE i int;
      DECLARE str varchar(255);
      SET i = 0;
      WHILE i < 32 DO
               SET str = CONCAT('col_',i);
               SET @sql = CONCAT('ALTER TABLE persons ADD ',str,' float;');
               SET i = i + 1;
               PREPARE stmt FROM @sql;
               EXECUTE stmt;
               DEALLOCATE PREPARE stmt;
      END WHILE;
 END
DELIMITER ;
CALL MYLOOP();

但是我得到这个错误:

#1305-过程database_sensor1.MYLOOP不存在

#1305 - PROCEDURE database_sensor1.MYLOOP does not exist

我试图通过快速更改表以添加32列的方式使用过程来循环创建表，以便我可以运行另一个.php来添加值.

I am trying to use the Procedure to Loop the table creation by quickly altering the table to add 32 columns in so that I can run another .php to add in the values.

推荐答案

CREATE DATABASE database_Sensor1;
USE database_Sensor1;
CREATE TABLE database_Sensor1.Persons(id int NOT NULL);
DELIMITER //
CREATE PROCEDURE database_Sensor1.MYLOOP()
BEGIN 
      DECLARE i int;
      DECLARE str varchar(255);
      SET i = 0;
      WHILE i < 32 DO
               SET str = CONCAT('col_',i);
               SET @sql = CONCAT('ALTER TABLE database_Sensor1.persons ADD ',str,' float;');
               SET i = i + 1;
               PREPARE stmt FROM @sql;
               EXECUTE stmt;
               DEALLOCATE PREPARE stmt;
      END WHILE;
 END
//
DELIMITER ;
CALL MYLOOP();

版本:

1. IF database_name EXISTS 结构不存在.已移除.参见第3版.
2. 过程创建代码未使用定界符完成.已添加.
3. 未明确指定必须在其中创建过程的数据库.添加.如果数据库不存在，则将生成错误.与表名相同.

1. IF database_name EXISTS construction not exists. Removed. See edition 3.
2. Procedure creation code is not finalized with delimiter. Added.
3. The database in which the procedure must be created is not specified explicitly. Added. If the database not exists then the error will be generated. The same with the table name.

这篇关于在创建数据库和表之前创建过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！