mysql 获取表pk_获取MySQL5.5表元信息

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