获取MySQL 5.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获取Oracle10g表元数据RHEL AS4 上Oracle 10g R2 静默安装相关资讯 MySQL基础教程

MySQL基础教程：关于varchar（N）（01月22日）
MySQL SELECT同时UPDATE同一张表（02/19/2013 07:20:18）
Linux修改MySQL最大并发连接数（02/15/2013 15:37:21）

高性能MySQL（第3版）中文PDF带目（10/26/2014 10:03:50）
如何在MySQL中的获取IP地址的网段（02/18/2013 12:23:33）
C++和C#访问MySQL的简单代码示例（12/21/2012 09:04:10）

本文评论查看全部评论（0）