获取MySQL5.5表元信息
 
最近,在写一个代码工具,从数据读取表生成实体Bean、SQLMap,需要获取到数据库的元信息,为了适应不同的数据库,需要针对每一种数据库实现一种获取元信息的方式,这里是MySQL5.5元信息的获取方式:
 
1、获取一个数据库下面所有的表
SELECT t.table_name,t.table_comment,t.create_time,
FROM information_schema.tables t
WHERE t.table_schema = SCHEMA();
 
2、获取一个表的元信息
SELECT t.column_name,
             t.data_type,
             CAST(SUBSTR(t.column_type, INSTR(t.column_type, '(') + 1, INSTR(t.column_type,')') - INSTR(t.column_type, '(') - 1) AS CHAR(20)) data_length,
             CAST(t.column_type AS CHAR(20)) column_type,
             t.column_comment,
             IF (t.is_nullable='YES',1,0) is_nullable,
             IF (t.column_key = 'PRI', 1, 0) is_key
FROM information_schema.columns t
WHERE t.table_schema = SCHEMA() AND
            t.table_name = '表名'
ORDER BY t.ordinal_position;
 
3、获取一个表的建表语句
SHOW CREATE TABLE 表名;
 
或者另外一种方式:
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