费了老大力气了,出现了一些莫名其妙的问题;mysql> show create table t10; +-------+-------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------+ | t10 | CREATE TABLE `t10` ( `A` varchar(200) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)mysql> insert into t10 values('192.168.1.11'),('192.168.1.12'),('192.168.2.11'),('192.168.2.12'),('192.168.3.11'); Query OK, 5 rows affected, 5 warnings (0.00 sec) Records: 5 Duplicates: 0 Warnings: 5mysql> show warnings; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1264 | Out of range value for column 'a' at row 1 | | Warning | 1264 | Out of range value for column 'a' at row 3 | | Warning | 1264 | Out of range value for column 'a' at row 5 | | Warning | 1264 | Out of range value for column 'a' at row 7 | | Warning | 1264 | Out of range value for column 'a' at row 9 | +---------+------+--------------------------------------------+ 5 rows in set (0.00 sec) 插入居然报错。再查也能查出来。但貌似实际存储的是12个0。用游标去循环的时候,得到的全是0。没辙,重新建个表,插入数据,正常,没有报错了。
存储过程如下:drop procedure if exists Create_Table; CREATE PROCEDURE Create_Table( ) BEGIN DECLARE done INT DEFAULT 0; DECLARE a varchar(200) ; DECLARE b varchar(200) ; DECLARE cur1 CURSOR FOR select concat(LPAD(SUBSTRING_INDEX(addr,'.',1),3,'0'), LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(addr,'.',2),'.',-1),3,'0'), LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(addr,'.',3),'.',-1),3,'0'), LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(addr,'.',4),'.',-1),3,'0')) as addr from create_table; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1; REPEAT FETCH cur1 INTO a; IF NOT done THEN set b=concat('create table IP',a,'(colname1 int)'); SET @E=b; PREPARE stmt1 FROM @E; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; END IF; UNTIL done END REPEAT; CLOSE cur1; END; 把IP地址格式化这段比较麻烦,可能有更简便的方式。 其他的就是游标去循环,预处理语句去拼SQL了。
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| t10 | CREATE TABLE `t10` (
`A` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> insert into t10 values('192.168.1.11'),('192.168.1.12'),('192.168.2.11'),('192.168.2.12'),('192.168.3.11');
Query OK, 5 rows affected, 5 warnings (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 5mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
| Warning | 1264 | Out of range value for column 'a' at row 3 |
| Warning | 1264 | Out of range value for column 'a' at row 5 |
| Warning | 1264 | Out of range value for column 'a' at row 7 |
| Warning | 1264 | Out of range value for column 'a' at row 9 |
+---------+------+--------------------------------------------+
5 rows in set (0.00 sec)
插入居然报错。再查也能查出来。但貌似实际存储的是12个0。用游标去循环的时候,得到的全是0。没辙,重新建个表,插入数据,正常,没有报错了。
CREATE PROCEDURE Create_Table(
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a varchar(200) ;
DECLARE b varchar(200) ;
DECLARE cur1 CURSOR FOR
select concat(LPAD(SUBSTRING_INDEX(addr,'.',1),3,'0'),
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(addr,'.',2),'.',-1),3,'0'),
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(addr,'.',3),'.',-1),3,'0'),
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(addr,'.',4),'.',-1),3,'0')) as addr from create_table;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a;
IF NOT done THEN
set b=concat('create table IP',a,'(colname1 int)');
SET @E=b;
PREPARE stmt1 FROM @E;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END;
把IP地址格式化这段比较麻烦,可能有更简便的方式。
其他的就是游标去循环,预处理语句去拼SQL了。
mysql> create table create_table(addr varchar(300));
Query OK, 0 rows affected (0.01 sec)