利用php获取MySql表结构

最新推荐文章于 2022-02-15 22:23:23 发布

代码一天不写我浑森蓝廋

最新推荐文章于 2022-02-15 22:23:23 发布

阅读量9.1k

点赞数

分类专栏： PHP MySQL

本文链接：https://blog.csdn.net/zhang197093/article/details/70308407

版权

PHP 同时被 2 个专栏收录

46 篇文章 3 订阅

订阅专栏

MySQL

8 篇文章 0 订阅

订阅专栏

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