php获取mysql表结构_利用php获取MySql表结构

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

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 '';

print_r($rt);

echo '';

@$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!