Mysql常用知识

常用脚本

建立多表的脚本

#使用存储过程
delimiter //
CREATE PROCEDURE createTablesInBulk () BEGIN
	DECLARE
		i INT;
	DECLARE
		table_name VARCHAR ( 200 );
	DECLARE
		table_pre VARCHAR ( 200 );
	DECLARE
		sql_text VARCHAR ( 10000 );

	SET i = 0;

	SET table_name = '';

	SET table_pre = 't_order';

	SET sql_text = '';
	#小于10的数字的占位符填充
	WHILE
			i < 10 DO
		IF
			i < 10 THEN

				SET table_name = CONCAT( table_pre, "0", i );
			ELSE
				SET table_name = CONCAT( table_pre, i );

		END IF;

		SET sql_text = CONCAT( 'CREATE TABLE ', table_name, "(
       `ORDER_ID` bigint(10) NOT NULL,
       `USRE_ID` varchar(7) NOT NULL,
       `NUM` varchar(10) NOT NULL,
       `SENDER_NAME` varchar(255) NOT NULL,
       `SENDER_MOBILE` varchar(255) NOT NULL,
       `SENDER_ADDR` varchar(255) NOT NULL,
       `RECEIVE_NAME` varchar(255) NOT NULL,
       `RECEIVE_MOBILE` varchar(255) NOT NULL,
       `RECEIVE_ADDR` varchar(255) NOT NULL,
       `HASH` char(32) NOT NULL,
       `COM` varchar(32) NOT NULL,
       UNIQUE KEY `orderId` (`ORDER_ID`) USING HASH) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
		SET @sql_text = sql_text;
		PREPARE stmt
		FROM
			@sql_text;
		EXECUTE stmt;

		SET i = i + 1;

	END WHILE;

END;

#调用
CALL createTablesInBulk ();

#删除程序
DROP PROCEDURE createTablesInBulk;

生成清空相似表名的语句

#模糊匹配表名product_stocket_
Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE 'product_stocket_%';
#清空表 t_order1
TRUNCATE t_order1