MySQL存储过程建表

源码 ( 注：因为用到 TABLE_NAME 列，所以参数用 I_table_name，否则如：Where 条件一直为 true，参数与变量命名最好不要与列名相同 )

出现问题关键还是要学会 Select 输出判断

DROP PROCEDURE IF EXISTS `createEvninfoTable`;

CREATE PROCEDURE `createEvninfoTable`(IN I_table_name varchar(100))
BEGIN  

	DECLARE i_len INT DEFAULT 0;
	DECLARE str_sql VARCHAR(5000); 
	SET @op_sql = '';
	SELECT CHARACTER_MAXIMUM_LENGTH INTO i_len FROM information_schema.COLUMNS WHERE TABLE_NAME = I_table_name AND COLUMN_NAME='id';
	IF (i_len > 0 AND i_len < 60) THEN
		SET @op_sql = CONCAT('ALTER TABLE ', I_table_name, ' MODIFY COLUMN `id` varchar(60) NOT NULL');
		prepare stmt from @op_sql;
		execute stmt;
		deallocate prepare stmt;
	END IF;

	IF (i_len = 0) THEN
		SET str_sql = CONCAT('CREATE TABLE IF NOT EXISTS ', I_table_name);
		SET str_sql = CONCAT(str_sql, '(
			`id` varchar(60) NOT NULL, \n
			`time` datetime NOT NULL,\n
			`log_server_id` int(11) NOT NULL,\n
			`log_sematic_id` int(11) NOT NULL,\n
			`server_ip` varchar(20) NOT NULL,\n
			`server_port` int(11) NOT NULL,\n
			`server_mac` varchar(45) NOT NULL,\n
			`client_ip` varchar(20) NOT NULL,\n
			`client_port` int(11) NOT NULL,\n
			`client_mac` varchar(45) NOT NULL,\n
			`ip_protocol` int(4) NOT NULL DEFAULT \'1\',\n
			`db_protocol` int(4) NOT NULL DEFAULT \'0\',\n
			`client_name` varchar(45) DEFAULT NULL,\n
			`statement` mediumtext NOT NULL,\n
			`object` varchar(100) DEFAULT NULL,\n
			`elapse` int(11) DEFAULT NULL,\n
			`u_sec` int(11) DEFAULT \'0\',\n
			`sec` int(11) DEFAULT \'0\',\n
			`affected_row` int(11) DEFAULT NULL,\n
			`ex_result` tinyint(4) DEFAULT NULL,\n
			`log_client_id` int(11) DEFAULT NULL,\n
			`log_strategy_ids` varchar(100) DEFAULT NULL,\n
			`statement_value` varchar(4000) DEFAULT NULL,\n
			`is_Filter` int(11) DEFAULT \'0\',\n
			`log_level` int(11) DEFAULT \'0\',\n
			`session_id` varchar(30) DEFAULT NULL,\n
			`xml` longtext COMMENT \'存放Select查询语句-返回的数据结果\',\n
			PRIMARY KEY (`id`),\n
			KEY `time` (`time`) USING BTREE,\n
			KEY `client_ip` (`client_ip`) USING BTREE\n
		) ENGINE=MyISAM DEFAULT CHARSET=utf8;\n');

		SET @ttt = str_sql;
		prepare stmt from @ttt;
		execute stmt;
		DEALLOCATE PREPARE stmt;
	END IF;

END;

CALL createEvninfoTable('t_evninfo_20160310_149a');