利用php获取MySql表结构

来源：互联网发布：无印良品淘宝假货编辑：程序博客网时间：2024/06/05 20:12

有时我们会需要在php代码中来获取mysql表结构，主要用到 "SHOW COLUMNS FROM onetable" sql语句，下面是代码示例

<?php$db = new mysqli('localhost', 'root', '123456', 'test');if ($db->connect_errno){    die("数据库连接失败: " . $db->connect_error);}$res = $db->query("SHOW COLUMNS FROM `test`");$rt = array();if ($res instanceof mysqli_result){    while (($row = $res->fetch_assoc()) != FALSE)    {        $row['CanBeNull'] = $row['Null'] === 'YES';   //字段值是否可以为空，是的话值为'YES'        $rt[] = $row;    }}echo '<pre>';print_r($rt);echo '</pre>';@$db->close();

执行结果：

Array(    [0] => Array        (            [Field] => uid      /* 字段名 */            [Type] => smallint(6)            [Null] => NO            [Key] => PRI    /* 主键为PRI 一般索引为MUL */            [Default] =>            [Extra] => auto_increment  /* 自增长 */            [CanBeNull] =>        )    [1] => Array        (            [Field] => username            [Type] => varchar(20)            [Null] => YES            [Key] =>            [Default] =>            [Extra] =>            [CanBeNull] => 1        )    [2] => Array        (            [Field] => userpwd            [Type] => varchar(32)            [Null] => YES            [Key] =>            [Default] =>            [Extra] =>            [CanBeNull] => 1        )    [3] => Array        (            [Field] => status            [Type] => tinyint(1)            [Null] => YES            [Key] => MUL   /* 一般索引为MUL */            [Default] => 1            [Extra] =>            [CanBeNull] => 1        )    [4] => Array        (            [Field] => name            [Type] => varchar(25)            [Null] => YES            [Key] =>            [Default] =>            [Extra] =>            [CanBeNull] => 1        )    [5] => Array        (            [Field] => mobile            [Type] => varchar(20)            [Null] => NO            [Key] =>            [Default] =>            [Extra] =>            [CanBeNull] =>        )    [6] => Array        (            [Field] => lever            [Type] => text            [Null] => YES            [Key] =>            [Default] =>            [Extra] =>            [CanBeNull] => 1        )    [7] => Array        (            [Field] => roleid            [Type] => tinyint(1)            [Null] => YES            [Key] =>            [Default] => 1            [Extra] =>            [CanBeNull] => 1        ))

That's it!

0 0