mysql存储过程循环分表

2019独角兽企业重金招聘Python工程师标准>>>

当一张表的数据达到几千万时，你查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。

mysql> DELIMITER $$

mysql> CREATE procedure wx_user_fans()

    -> BEGIN

    -> DECLARE `@i` int(11);

    -> DECLARE `@sqlstr` varchar(2560);

    -> SET `@i`=0;

    -> WHILE `@i` < 10 DO

    -> SET @sqlstr = CONCAT(

    -> "CREATE TABLE wx_user_fans_",`@i`,

    -> "( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` int(11) DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 "

    -> );

    -> prepare stmt from @sqlstr;

    -> execute stmt;

    ->

    -> SET `@i` = `@i` + 1;

    -> END WHILE;

    -> end $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> call wx_user_fans();

Query OK, 0 rows affected (0.13 sec)

mysql> drop procedure wx_user_fans;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| admin          |

| files          |

| mytest         |

| tb_orderlist   |

| wx_user_fans_0 |

| wx_user_fans_1 |

| wx_user_fans_2 |

| wx_user_fans_3 |

| wx_user_fans_4 |

| wx_user_fans_5 |

| wx_user_fans_6 |

| wx_user_fans_7 |

| wx_user_fans_8 |

| wx_user_fans_9 |

| zcctest        |

+----------------+

15 rows in set (0.00 sec)

转载于:https://my.oschina.net/shanghaifengchen/blog/603988