获取MySQL5.5表元信息

ndlli

CREATE    FUNCTION `get_tab_ddl`(p_tab_name VARCHAR(100)) RETURNS text　　NO SQL
　　DETERMINISTIC
　　BEGIN
　　DECLARE v_return TEXT DEFAULT '';
　　DECLARE v_ddl TEXT DEFAULT '';
　　#表信息相关变量
　　DECLARE v_engine VARCHAR(255);
　　DECLARE v_row_format VARCHAR(255);
　　DECLARE v_tab_comment VARCHAR(255);
　　#约束相关变量
　　DECLARE v_cons_name VARCHAR(255);
　　DECLARE v_cons_type VARCHAR(255);
　　#约束列相关变量
　　DECLARE v_cons_col_name VARCHAR(255);
　　DECLARE v_ref_tab_schema VARCHAR(255);
　　DECLARE v_ref_tab_name VARCHAR(255);
　　DECLARE v_ref_col_name VARCHAR(255);
　　DECLARE v_update_rule    VARCHAR(255);
　　DECLARE v_delete_rule    VARCHAR(255);
　　#索引相关变更
　　DECLARE v_index_name VARCHAR(255);
　　DECLARE v_l_index_name VARCHAR(255) DEFAULT '';
　　DECLARE v_ind_col_name VARCHAR(255);
　　DECLARE v_done INT DEFAULT 0;
　　#列游标
　　DECLARE cur_column CURSOR FOR
　　SELECT CONCAT('    ','`',t.column_name,'` ',column_type,
　　IF(t.is_nullable = 'NO',' NOT NULL',''),
　　IF(t.extra IS NULL,'',CONCAT(' ',t.extra)),
　　IF(t.column_default IS NULL,'',CONCAT(' ','DEFAULT ' , "'" , t.column_default , "'")),
　　IF(t.column_comment = '','',CONCAT(' ','COMMENT ' , "'" , t.column_comment , "'")),',') tab_column
　　FROM information_schema.columns t
　　WHERE t.table_schema = SCHEMA()
　　AND t.table_name = p_tab_name
　　ORDER BY t.ordinal_position;
　　#主键约束
　　DECLARE cur_pk CURSOR FOR
　　SELECT t.column_name
　　FROM information_schema.key_column_usage t
　　WHERE t.table_schema = SCHEMA()
　　AND t.table_name = p_tab_name
　　AND t.constraint_name = 'PRIMARY'
　　ORDER BY t.ordinal_position;
　　#其它约束游标
　　DECLARE cur_cons CURSOR FOR
　　SELECT t.constraint_type,t.constraint_name
　　FROM information_schema.table_constraints t
　　WHERE t.table_schema = SCHEMA()
　　AND t.table_name = p_tab_name
　　AND t.constraint_type  'PRIMARY KEY';
　　#约束列游标
　　DECLARE cur_col_cons CURSOR FOR
　　SELECT t.column_name,t.referenced_table_schema,t.referenced_table_name,t.referenced_column_name,c.update_rule,c.delete_rule
　　FROM information_schema.key_column_usage t
　　LEFT JOIN information_schema.referential_constraints c ON (t.table_name = c.table_name AND t.constraint_name = c.constraint_name)
　　WHERE t.table_schema = SCHEMA()
　　AND t.table_name = p_tab_name
　　AND t.constraint_name = v_cons_name
　　ORDER BY t.ordinal_position;
　　#表上索引游标
　　DECLARE cur_index CURSOR FOR
　　SELECT t.index_name,t.column_name
　　FROM information_schema.statistics t
　　WHERE t.table_schema = SCHEMA()
　　AND t.table_name = p_tab_name
　　AND NOT EXISTS (SELECT 1 FROM information_schema.table_constraints c
　　WHERE t.table_schema = c.table_schema
　　AND t.table_name = c.table_name
　　AND t.index_name = c.constraint_name)
　　ORDER BY t.index_name,t.seq_in_index;
　　DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done=1;
　　#表信息
　　SELECT IF(t.engine = '' OR t.engine IS NULL,'',CONCAT(' ENGINE=',t.engine)) ENGINE,
　　t.row_format,
　　IF(t.table_comment = '' OR t.table_comment IS NULL,'',CONCAT(" COMMENT='",t.table_comment,"'")) table_comment
　　INTO v_engine,v_row_format,v_tab_comment
　　FROM information_schema.tables t
　　WHERE t.table_schema = SCHEMA()
　　AND t.table_name = p_tab_name;
　　SET v_return = CONCAT('CREATE TABLE `',p_tab_name,'` (',CHAR(13));
　　#打开列游标
　　OPEN cur_column;
　　FETCH cur_column INTO v_ddl;
　　WHILE v_done  1 DO
　　SET v_return = CONCAT(v_return,v_ddl,CHAR(13));
　　FETCH cur_column INTO v_ddl;
　　END WHILE;
　　CLOSE cur_column;
　　SET v_ddl = '';
　　#打开主键约束
　　SET v_done = 0;
　　OPEN cur_pk ;
　　FETCH cur_pk INTO v_cons_col_name;
　　WHILE v_done  1 DO
　　SET v_ddl = CONCAT(v_ddl,'`',v_cons_col_name,'`,');
　　FETCH cur_pk INTO v_cons_col_name;
　　END WHILE;
　　CLOSE cur_pk;
　　IF v_ddl  '' THEN
　　SET v_return = CONCAT(v_return,'    ','PRIMARY KEY (',LEFT(v_ddl,CHAR_LENGTH(v_ddl) - 1),'),',CHAR(13));
　　END IF;
　　SET v_return = CONCAT(LEFT(v_return,CHAR_LENGTH(v_return) - 2),CHAR(13));
　　SET v_return = CONCAT(v_return,') ',v_engine,v_tab_comment,' ;',CHAR(13));
　　#打开其它约束游标
　　SET v_done = 0;
　　OPEN cur_cons;
　　FETCH cur_cons INTO v_cons_type,v_cons_name;
　　WHILE v_done  1 DO
　　IF v_cons_type = 'FOREIGN KEY' THEN
　　SET v_return = CONCAT(v_return,CHAR(13),'ALTER TABLE `',p_tab_name,'` ADD CONSTRAINT `',v_cons_name,'` FOREIGN KEY (');
　　#打开外键约束列游标
　　OPEN cur_col_cons;
　　FETCH cur_col_cons INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;
　　WHILE v_done  1 DO
　　SET v_return = CONCAT(v_return,'`',v_cons_col_name,'`) REFERENCES `',v_ref_tab_name,'` (`',v_ref_col_name,'`) ',
　　'ON DELETE ',v_delete_rule,' ON UPDATE ',v_update_rule);
　　FETCH cur_col_cons INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;
　　END WHILE;
　　CLOSE cur_col_cons;
　　SET v_return = CONCAT(v_return,';',CHAR(13));
　　ELSE
　　SET v_return = CONCAT(v_return,CHAR(13),'ALTER TABLE `',p_tab_name,'` ADD CONSTRAINT `',v_cons_name,'` UNQINE (');
　　#打开唯一约束列游标
　　OPEN cur_col_cons;
　　FETCH cur_col_cons INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;
　　WHILE v_done  1 DO
　　SET v_return = CONCAT(v_return,'`',v_cons_col_name,'`,');
　　FETCH cur_col_cons INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;
　　END WHILE;
　　CLOSE cur_col_cons;
　　SET v_return = CONCAT(LEFT(v_return,CHAR_LENGTH(v_return) - 1),');',CHAR(13));
　　END IF;
　　SET v_done = 0;
　　FETCH cur_cons INTO v_cons_type,v_cons_name;
　　END WHILE;
　　CLOSE cur_cons;
　　#打开索引游标
　　SET v_done = 0;
　　SET v_ddl = '';
　　OPEN cur_index;
　　FETCH cur_index INTO v_index_name,v_ind_col_name;
　　WHILE v_done  1 DO
　　IF v_index_name = v_l_index_name THEN
　　SET v_ddl = CONCAT(v_ddl,'`',v_ind_col_name,'`,');
　　ELSEIF v_l_index_name IS NULL OR v_l_index_name = '' THEN
　　SET v_ddl = CONCAT(v_ddl,CHAR(13),'CREATE INDEX `',v_index_name,'` ON `',p_tab_name,'` (`',v_ind_col_name,'`,');
　　ELSE
　　SET v_ddl = CONCAT(LEFT(v_ddl,CHAR_LENGTH(v_ddl) - 1),');',CHAR(13),CHAR(13),'CREATE INDEX `',
　　v_index_name,'` ON `',p_tab_name,'` (`',v_ind_col_name,'`,');
　　END IF;
　　SET v_l_index_name = v_index_name;
　　FETCH cur_index INTO v_index_name,v_ind_col_name;
　　END WHILE;
　　CLOSE cur_index;
　　IF v_ddl  '' THEN
　　SET v_return = CONCAT(v_return,LEFT(v_ddl,CHAR_LENGTH(v_ddl) - 1),');',CHAR(13));
　　END IF;
　　RETURN v_return;
　　END