PHP获取mysql数据表的字段名称及详细属性

凯隐 0     2044      数据库   0     0
最近想做一些功能，获取到数据表的中英文名称、数据表字段的一些信息，但是框架里面的信息不全，不是我想要的，就百度了下数据库的原生操作，记录一下

基本命令：

SHOW DATABASES                                //列出 MySQL Server 数据库。
SHOW TABLES [FROM db_name]                    //列出数据库数据表。
SHOW CREATE TABLES tbl_name                    //导出数据表结构。
SHOW TABLE STATUS [FROM db_name]              //列出数据表及表状态信息。
SHOW COLUMNS FROM tbl_name [FROM db_name]     //列出资料表字段
SHOW FIELDS FROM tbl_name [FROM db_name]，DESCRIBE tbl_name [col_name]。SHOW FULL COLUMNS FROM tbl_name [FROM db_name]//列出字段及详情
SHOW FULL FIELDS FROM tbl_name [FROM db_name] //列出字段完整属性
SHOW INDEX FROM tbl_name [FROM db_name]       //列出表索引。
SHOW STATUS                                  //列出 DB Server 状态。
SHOW VARIABLES                               //列出 MySQL 系统环境变量。
SHOW PROCESSLIST                             //列出执行命令。
SHOW GRANTS FOR user                         //列出某用户权限
DESC TABLE                                  //获取表详细信息


调试结果：

使用的框架：ThinkPHP5.0.23

1.SHOW DATABASES             //列出 MySQL Server 数据库

        //读取数据表列表

        //读取数据表列表

        $DB = Db::connect();

        

        $var = $DB->query('SHOW DATABASES');

        

        dump($var);

结果：



  [2] => array(1) {

    ["Database"] => string(5) "mysql"

  }

  [3] => array(1) {

    ["Database"] => string(18) "performance_schema"

  }

  [4] => array(1) {

    ["Database"] => string(4) "test"

  }

  [5] => array(1) {

    ["Database"] => string(6) "traval"

  }


2. SHOW TABLES [FROM db_name]                    //列出数据库数据表    

        //读取数据表列表
        $DB = Db::connect();
        
        $var = $DB->query('SHOW TABLES FROM article');
        
        dump($var);

运行结果：

array(65) {
  [0] => array(1) {
    ["Tables_in_article"] => string(10) "cto_action"
  }
  [1] => array(1) {
    ["Tables_in_article"] => string(13) "cto_advertise"
  }
  [2] => array(1) {
    ["Tables_in_article"] => string(17) "cto_advertisement"
  }
  [3] => array(1) {
    ["Tables_in_article"] => string(10) "cto_answer"
  }
  [4] => array(1) {
    ["Tables_in_article"] => string(11) "cto_article"
  }
  [5] => array(1) {
    ["Tables_in_article"] => string(20) "cto_article_category"
  }
}


3.  SHOW TABLE STATUS [FROM db_name]              //列出数据表及表状态信息

        //读取数据表列表
        $DB = Db::connect();
        
        $var = $DB->query('SHOW TABLE STATUS FROM article');
        
        dump($var);

运行结果：

array(65) {
  [0] => array(18) {
    ["Name"] => string(10) "cto_action"
    ["Engine"] => string(6) "InnoDB"
    ["Version"] => int(10)
    ["Row_format"] => string(7) "Compact"
    ["Rows"] => int(1)
    ["Avg_row_length"] => int(16384)
    ["Data_length"] => int(16384)
    ["Max_data_length"] => int(0)
    ["Index_length"] => int(16384)
    ["Data_free"] => int(10485760)
    ["Auto_increment"] => int(2)
    ["Create_time"] => string(19) "2018-12-28 14:33:36"
    ["Update_time"] => NULL
    ["Check_time"] => NULL
    ["Collation"] => string(15) "utf8_general_ci"
    ["Checksum"] => NULL
    ["Create_options"] => string(0) ""
    ["Comment"] => string(19) "系统表-动作表"
  }
  [1] => array(18) {
    ["Name"] => string(13) "cto_advertise"
    ["Engine"] => string(6) "InnoDB"
    ["Version"] => int(10)
    ["Row_format"] => string(7) "Compact"
    ["Rows"] => int(10)
    ["Avg_row_length"] => int(1638)
    ["Data_length"] => int(16384)
    ["Max_data_length"] => int(0)
    ["Index_length"] => int(0)
    ["Data_free"] => int(10485760)
    ["Auto_increment"] => int(16)
    ["Create_time"] => string(19) "2018-12-28 14:33:36"
    ["Update_time"] => NULL
    ["Check_time"] => NULL
    ["Collation"] => string(15) "utf8_general_ci"
    ["Checksum"] => NULL
    ["Create_options"] => string(0) ""
    ["Comment"] => string(6) "广告"
  }
  [2] => array(18) {
    ["Name"] => string(17) "cto_advertisement"
    ["Engine"] => string(6) "InnoDB"
    ["Version"] => int(10)
    ["Row_format"] => string(7) "Compact"
    ["Rows"] => int(5)
    ["Avg_row_length"] => int(3276)
    ["Data_length"] => int(16384)
    ["Max_data_length"] => int(0)
    ["Index_length"] => int(16384)
    ["Data_free"] => int(10485760)
    ["Auto_increment"] => int(10)
    ["Create_time"] => string(19) "2018-12-28 14:33:36"
    ["Update_time"] => NULL
    ["Check_time"] => NULL
    ["Collation"] => string(15) "utf8_general_ci"
    ["Checksum"] => NULL
    ["Create_options"] => string(0) ""
    ["Comment"] => string(9) "广告位"
  }
}


4.  SHOW COLUMNS FROM tbl_name [FROM db_name]     //列出资料表字段

        //读取数据表列表
        $DB = Db::connect();
        
        $var = $DB->query('SHOW COLUMNS FROM cto_answer FROM article');
        
        dump($var);

运行结果：

array(8) {
  [0] => array(6) {
    ["Field"] => string(2) "id"
    ["Type"] => string(21) "mediumint(8) unsigned"
    ["Null"] => string(2) "NO"
    ["Key"] => string(3) "PRI"
    ["Default"] => NULL
    ["Extra"] => string(14) "auto_increment"
  }
  [1] => array(6) {
    ["Field"] => string(11) "question_id"
    ["Type"] => string(21) "mediumint(8) unsigned"
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
  }
  [2] => array(6) {
    ["Field"] => string(7) "content"
    ["Type"] => string(4) "text"
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
  }
  [3] => array(6) {
    ["Field"] => string(5) "views"
    ["Type"] => string(21) "mediumint(8) unsigned"
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => string(1) "0"
    ["Extra"] => string(0) ""
  }
  [4] => array(6) {
    ["Field"] => string(6) "status"
    ["Type"] => string(19) "tinyint(2) unsigned"
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => string(1) "1"
    ["Extra"] => string(0) ""
  }
  [5] => array(6) {
    ["Field"] => string(11) "create_time"
    ["Type"] => string(16) "int(11) unsigned"
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
  }
  [6] => array(6) {
    ["Field"] => string(10) "create_uid"
    ["Type"] => string(16) "int(11) unsigned"
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
  }
  [7] => array(6) {
    ["Field"] => string(11) "delete_time"
    ["Type"] => string(16) "int(11) unsigned"
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
  }
}


5. SHOW FULL COLUMNS FROM tbl_name [FROM db_name]//列出字段及详情

        //读取数据表列表
        $DB = Db::connect();
        
        $var = $DB->query('SHOW FULL COLUMNS FROM cto_answer FROM article');
        
        dump($var);

运行结果：

array(8) {
  [0] => array(9) {
    ["Field"] => string(2) "id"
    ["Type"] => string(21) "mediumint(8) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(3) "PRI"
    ["Default"] => NULL
    ["Extra"] => string(14) "auto_increment"
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [1] => array(9) {
    ["Field"] => string(11) "question_id"
    ["Type"] => string(21) "mediumint(8) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [2] => array(9) {
    ["Field"] => string(7) "content"
    ["Type"] => string(4) "text"
    ["Collation"] => string(15) "utf8_general_ci"
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [3] => array(9) {
    ["Field"] => string(5) "views"
    ["Type"] => string(21) "mediumint(8) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => string(1) "0"
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [4] => array(9) {
    ["Field"] => string(6) "status"
    ["Type"] => string(19) "tinyint(2) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => string(1) "1"
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [5] => array(9) {
    ["Field"] => string(11) "create_time"
    ["Type"] => string(16) "int(11) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [6] => array(9) {
    ["Field"] => string(10) "create_uid"
    ["Type"] => string(16) "int(11) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [7] => array(9) {
    ["Field"] => string(11) "delete_time"
    ["Type"] => string(16) "int(11) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
}



6. SHOW FULL FIELDS FROM tbl_name [FROM db_name] //列出字段完整属性

        //读取数据表列表
        $DB = Db::connect();    
        
        $var = $DB->query('SHOW FULL FIELDS FROM cto_answer FROM article');
        
        dump($var);

运行结果：

array(8) {
  [0] => array(9) {
    ["Field"] => string(2) "id"
    ["Type"] => string(21) "mediumint(8) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(3) "PRI"
    ["Default"] => NULL
    ["Extra"] => string(14) "auto_increment"
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [1] => array(9) {
    ["Field"] => string(11) "question_id"
    ["Type"] => string(21) "mediumint(8) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [2] => array(9) {
    ["Field"] => string(7) "content"
    ["Type"] => string(4) "text"
    ["Collation"] => string(15) "utf8_general_ci"
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [3] => array(9) {
    ["Field"] => string(5) "views"
    ["Type"] => string(21) "mediumint(8) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => string(1) "0"
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [4] => array(9) {
    ["Field"] => string(6) "status"
    ["Type"] => string(19) "tinyint(2) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => string(1) "1"
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [5] => array(9) {
    ["Field"] => string(11) "create_time"
    ["Type"] => string(16) "int(11) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [6] => array(9) {
    ["Field"] => string(10) "create_uid"
    ["Type"] => string(16) "int(11) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
  [7] => array(9) {
    ["Field"] => string(11) "delete_time"
    ["Type"] => string(16) "int(11) unsigned"
    ["Collation"] => NULL
    ["Null"] => string(2) "NO"
    ["Key"] => string(0) ""
    ["Default"] => NULL
    ["Extra"] => string(0) ""
    ["Privileges"] => string(31) "select,insert,update,references"
    ["Comment"] => string(0) ""
  }
}



7. 其他的自行调试



    

相关文章

154
文章总数
141
评论总数
86
会员总数

欢迎打赏投稿

我要打赏
captcha
忘记密码？ 注册
第三方登录
微信赞赏
支付宝赞赏