Php实现把json生成mysql表结构和插入sql语句2_php 读取json文件并写入sql-CSDN博客

<?php

class JsonToSql {
    /**
     * 从JSON生成SQL CREATE TABLE语句的函数
     *
     * @param array $json JSON数据数组
     * @param string $table_name 表名
     * @param int $level 层级
     * @param array &$processed_tables 已处理的表名集合
     * @return array 包含表名和SQL语句的数组
     */
    public static function parse_json($json, $table_name = 'root', $level = 0, &$processed_tables = []) {
        $tables = [];
        $columns = ["id VARCHAR(255) PRIMARY KEY"]; // 默认每张表有个主键id

        foreach ($json as $key => $value) {
            if ($key === 'id') {
                $key = 'unique_id'; // 防止重复列名
            }
            if (is_array($value) && self::is_assoc($value)) {
                // 递归处理嵌套的对象
                $sub_table_name = $table_name . '_' . $key;
                $sub_tables = self::parse_json($value, $sub_table_name, $level + 1, $processed_tables);
                $tables = array_merge($tables, $sub_tables);
                $columns[] = "$key INT COMMENT 'Sub-table record count'";
            } elseif (is_array($value)) {
                // 处理数组，数组里面是对象的情况
                if (self::is_assoc($value[0])) {
                    $sub_table_name = $table_name . '_' . $key;
                    foreach ($value as $index => $sub_value) {
                        if (!in_array($sub_table_name, $processed_tables)) {
                            $sub_tables = self::parse_json($sub_value, $sub_table_name, $level + 1, $processed_tables);
                            $tables = array_merge($tables, $sub_tables);
                            $processed_tables[] = $sub_table_name;
                        }
                    }
                    $columns[] = "$key INT COMMENT 'Array length: " . count($value) . "'";
                } else {
                    // 对于子项是数组的情况，只记录数组长度
                    $columns[] = "$key INT COMMENT 'Array length: " . count($value) . "'";
                }
            } else {
                $column_type = self::get_column_type($value);
                $columns[] = "$key $column_type COMMENT 'Example data: $value'";
            }
        }

        $columns_sql = implode(", ", $columns);
        $create_table_sql = "CREATE TABLE IF NOT EXISTS `$table_name` ($columns_sql)";
        $tables[] = ["table_name" => $table_name, "sql" => $create_table_sql];

        return $tables;
    }

    /**
     * 判断数组是否为关联数组的函数
     *
     * @param array $array 输入数组
     * @return bool 是否为关联数组
     */
    public static function is_assoc($array) {
        return array_keys($array) !== range(0, count($array) - 1);
    }

    /**
     * 根据值类型获取SQL数据类型的函数
     *
     * @param mixed $value 输入值
     * @return string SQL数据类型
     */
    public static function get_column_type($value) {
        if (is_int($value)) {
            return "INT";
        } elseif (is_float($value)) {
            return "FLOAT";
        } elseif (is_bool($value)) {
            return "TINYINT(1)";
        } else {
            return "VARCHAR(255)";
        }
    }

    /**
     * 生成SQL INSERT语句的函数
     *
     * @param array $json JSON数据数组
     * @param string $table_name 表名
     * @param array &$insert_sqls INSERT语句集合
     */
    public static function generate_insert_sql($json, $table_name = 'root', &$insert_sqls = []) {
        $columns = array_keys($json);
        $values = array_map(function($value) {
            if (is_array($value)) {
                return count($value); // 只记录数组长度
            } elseif (is_null($value)) {
                return 'NULL';
            } else {
                return "'" . str_replace("'", "''", $value) . "'";
            }
        }, array_values($json));

        // 生成唯一ID
        $unique_id = uniqid();
        if (!in_array('id', $columns)) {
            array_unshift($columns, 'id');
            array_unshift($values, "'" . $unique_id . "'");
        }

        $columns_sql = implode(", ", $columns);
        $values_sql = implode(", ", $values);

        $insert_sqls[] = "INSERT INTO `$table_name` ($columns_sql) VALUES ($values_sql)";

        foreach ($json as $key => $value) {
            if (is_array($value) && self::is_assoc($value)) {
                // 递归处理嵌套的对象
                $sub_table_name = $table_name . '_' . $key;
                self::generate_insert_sql($value, $sub_table_name, $insert_sqls);
            } elseif (is_array($value) && self::is_assoc($value[0])) {
                // 处理数组，数组里面是对象的情况
                $sub_table_name = $table_name . '_' . $key;
                foreach ($value as $sub_value) {
                    self::generate_insert_sql($sub_value, $sub_table_name, $insert_sqls);
                }
            }
        }
    }

    public static function get_sql($json_data, $type = '') {
        // 将JSON字符串解码为数组
        $json_array = json_decode($json_data, true);

        // 生成表结构
        $processed_tables = [];
        $tables = self::parse_json($json_array, 'Airoot', 0, $processed_tables);

        // 输出生成的SQL CREATE TABLE语句
        $date = [];
        foreach ($tables as $table) {
            $date['add_table'][$table['table_name']] = $table['sql'];
            $date['sql'][] = $table['sql'];
        }

        // 生成并输出SQL INSERT语句
        $insert_sqls = [];
        self::generate_insert_sql($json_array, 'Airoot', $insert_sqls);
        foreach ($insert_sqls as $insert_sql) {
            $date['table_insert'][] = $insert_sql;
            $date['sql'][] = $insert_sql;
        }
        if ($type == "sql") {
            $_sql = '';
            foreach ($date['sql'] as $sql) {
                $_sql .= '<br/>' . $sql . ';';
            }
            echo $_sql;
        } elseif ($type == "array") {
            print_r($date);
        } else {
            echo json_encode($date, true);
        }
    }
}

// 示例使用
$json_data = '{"errCode":"0000","message":"有城市","data":{"id":"1476084112357362988eba11e682992c56dcba85b9","name":"杭州市","pid":"14760841123571a3848eba11e682992c56dcba85b9","pid_name":"浙江省","pid_shortname":"浙江","description":"杭州欢迎你！","mobile_thumb":"data/system_area/20160608/20160608095415_67470.jpg","new_mobile_thumb":null,"zyz_amount":4210864,"relation_arealevel":2,"relation_areaid":"1476084112357362988eba11e682992c56dcba85b9","relation_province":"14760841123571a3848eba11e682992c56dcba85b9","relation_city":"1476084112357362988eba11e682992c56dcba85b9","relation_county":"","goods_county":1,"sub":[{"id":"14760841123589dde68eba11e682992c56dcba85b9","name":"市辖区"},{"id":"14760841123589e00a8eba11e682992c56dcba85b9","name":"拱墅区"},{"id":"14760841123589e1a58eba11e682992c56dcba85b9","name":"西湖区"},{"id":"14760841123589e23b8eba11e682992c56dcba85b9","name":"余杭区"},{"id":"14760841123589e59f8eba11e682992c56dcba85b9","name":"建德市"},{"id":"14760841123589de6c8eba11e682992c56dcba85b9","name":"上城区"},{"id":"14760841123589e0948eba11e682992c56dcba85b9","name":"滨江区"},{"id":"14760841123589e11f8eba11e682992c56dcba85b9","name":"临平区"},{"id":"14760841123589e2c58eba11e682992c56dcba85b9","name":"桐庐县"},{"id":"14760841123589e34b8eba11e682992c56dcba85b9","name":"淳安县"},{"id":"14760841123589e62d8eba11e682992c56dcba85b9","name":"富阳区"},{"id":"14760841123589e6b48eba11e682992c56dcba85b9","name":"临安区"},{"id":"1635231704169856a4bedd7294c82bd94d47dd956e102","name":"临平区"},{"id":"1635231615363119404ea391c41f783d576318fdb254e","name":"钱塘区"},{"id":"1675763768159f1ef63929a234f0688cacddac4420def","name":"杭州西湖风景名胜区"}]}}';

// 输出最终SQL
echo JsonToSql::get_sql($json_data, 'sql');

?>