有时我们会需要在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
)
)