我有一个库mytest1,里面有一个表ipinfo保存了1000多条IP的记录,保存ip地址的字段是ipaddr。
我现在想利用ipinfo里的每个ip格式化后作为表名新建一个表。比如192.168.1.11格式化成IP192168001011,作为表名新建一个表。
请问sql语句该如何写啊?
我现在想利用ipinfo里的每个ip格式化后作为表名新建一个表。比如192.168.1.11格式化成IP192168001011,作为表名新建一个表。
请问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)