php 将 JSON 导入 Mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17367325/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Importing JSON into Mysql
提问by John
the problem that I am facing is importing JSON into Mysql. I have searched the interwebs, I have searched stackoverflow and probably even more sources, but could not find a single propersolution. Full disclosure here - I am not a PHP, SQL nor am I a JSON professional.
我面临的问题是将 JSON 导入 Mysql。我搜索了互联网，搜索了 stackoverflow 和可能更多的来源，但找不到一个合适的解决方案。在这里完全公开 - 我不是 PHP、SQL 也不是 JSON 专家。
What I am trying to accomplish is fairly simple: Import this JSON file https://developers.facebook.com/tools/explorer/method=GET&path=245226895508982%2Ffeed%3Faccess_token%3D%3Caccess_token%3E=into a Mysql database. I understand that I need so somehow allocate the JSON data into columns and then curate the rows accordingly with the content. I do not need all of the data, but most of it. The data should later be echoed through a search query from a user.
我想要完成的工作非常简单：将这个 JSON 文件https://developers.facebook.com/tools/explorer/method=GET&path=245226895508982%2Ffeed%3Faccess_token%3D%3Caccess_token%3E=导入到 Mysql 数据库中。我知道我需要以某种方式将 JSON 数据分配到列中，然后根据内容相应地管理行。我不需要所有数据，但需要大部分数据。稍后应通过用户的搜索查询回显数据。
My question is seems fairly simple - how can I do it?
我的问题似乎很简单 - 我该怎么做？
P.S: I tried converting it into XML, but I do not have the sufficient rights to import the XML into the SQL database. Also the converter did not the best job.
PS：我尝试将其转换为 XML，但我没有足够的权限将 XML 导入 SQL 数据库。转换器也不是最好的工作。
回答by kmas
You can export your json to csv : http://www.danmandle.com/blog/json-to-csv-conversion-utility/or https://github.com/danmandle/JSON2CSV
您可以将您的 json 导出到 csv ：http: //www.danmandle.com/blog/json-to-csv-conversion-utility/或https://github.com/danmandle/JSON2CSV
Then :
然后 ：
LOAD DATA INFILE 'filepath/your_csv_file.csv' INTO TABLE tablename;
It should be okay for a oneshot.
oneshot应该没问题。
More info for load data infile.
有关加载数据 infile 的更多信息。
回答by smashuu
Like others have said, you are going to have to do a little bit of conversion to do what you're asking. Fortunately, it's not too difficult to have PHP iterate through the data and do most of the work for you. Here's a quick-and-dirty attempt:
就像其他人所说的那样，您将不得不进行一些转换才能完成您的要求。幸运的是，让 PHP 遍历数据并为您完成大部分工作并不太难。这是一个快速而肮脏的尝试：
// MySQL table's name
$tableName = 'my_table';
// Get JSON file and decode contents into PHP arrays/values
$jsonFile = '/path/to/file.json';
$jsonData = json_decode(file_get_contents($jsonFile), true);
// Iterate through JSON and build INSERT statements
foreach ($jsonData as $id=>$row) {
$insertPairs = array();
foreach ($row as $key=>$val) {
$insertPairs[addslashes($key)] = addslashes($val);
}
$insertKeys = '`' . implode('`,`', array_keys($insertPairs)) . '`';
$insertVals = '"' . implode('","', array_values($insertPairs)) . '"';
echo "INSERT INTO `{$tableName}` ({$insertKeys}) VALUES ({$insertVals});" . "\n";
}
This will spit out a series of INSERT INTO my_table(...) VALUES (...);SQL statements that you can save to a .sql file and import into MySQL. With this method each INSERT can have different columns without causing problems.
这会吐出一系列INSERT INTO my_table(...) VALUES (...); 您可以将 SQL 语句保存到 .sql 文件并导入 MySQL。使用这种方法，每个 INSERT 可以有不同的列而不会引起问题。
Note: addslashes() isn't a very secure way of doing this, and something like real_escape_string() would be preferable, but this works without a DB connection assuming your JSON data is trustworthy.
注意：addslashes() 不是一种非常安全的方法，像 real_escape_string() 这样的方法会更可取，但假设您的 JSON 数据值得信赖，这在没有数据库连接的情况下工作。
回答by kgr
Using MySQL Shell 8.0.13you can import JSON documents straight to MySQL Server using util.importJsonshell API function or command line option --import. For example import from file:
使用MySQL Shell 8.0.13，您可以使用util.importJsonshell API 函数或命令行选项将 JSON 文档直接导入 MySQL 服务器--import。例如从文件导入：
mysqlsh user@host:port/database --import /tmp/facebook.json collection_name
mysqlsh user@host:port/database --import /tmp/facebook.json collection_name
or pipe JSON document to stdin:
或将 JSON 文档通过管道传输到标准输入：
cat /tmp/facebook.json | mysqlsh user@host:port/database --import - collection_name
cat /tmp/facebook.json | mysqlsh user@host:port/database --import - collection_name
where collection_nameis a collection.
这里collection_name是一个集合。
More about MySQL Shell's JSON Import utility: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-json.html
有关 MySQL Shell 的 JSON 导入实用程序的更多信息：https: //dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-json.html
More about document store: https://dev.mysql.com/doc/refman/8.0/en/document-store.html
更多关于文档存储：https: //dev.mysql.com/doc/refman/8.0/en/document-store.html
More about documents and collections: https://dev.mysql.com/doc/refman/8.0/en/mysql-shell-tutorial-javascript-documents-collections.html
有关文档和集合的更多信息：https: //dev.mysql.com/doc/refman/8.0/en/mysql-shell-tutorial-javascript-documents-collections.html
回答by user9526573
The following was tested and works on MySQL 8.0.18. I expect it to work on any version that supports JSON data type and load data statement.
以下内容已在 MySQL 8.0.18 上测试并有效。我希望它适用于支持 JSON 数据类型和加载数据语句的任何版本。
The JSON file can be loaded using load datastatement from any SQL client. Assuming the user has proper permission to run the statement and proper access to the file.
可以使用来自任何 SQL 客户端的加载数据语句加载 JSON 文件。假设用户具有运行语句的适当权限和对文件的适当访问权限。
create table jsonTableName( jsonColumnName json );
load data infile '/var/lib/mysql-files/someFile.json'
into table jsonTableName
fields terminated by '##代码##' escaped by ''
lines terminated by '##代码##';
Fieldsand lines terminated by '\0'disable fields and lines parsing, thus assuming that the entire file is a single cell.
字段和行终止由“\ 0”禁用字段和解析，从而假设整个文件是单细胞系。
escaped by ''disables the \to be interpreted by the loader. Thus allowing \"to be send directy to the JSON parser. Without it, any escape sequence will be loaded improperly potentially causing an exception.
由 '' 转义会禁用由加载程序解释的\。因此允许\"直接发送到 JSON 解析器。没有它，任何转义序列都将被错误加载，可能导致异常。
回答by Angel M.
I know is an old post, but maybe it can help someone. I wanted to create the same database in mysql like I have in mongoDb.
我知道这是一个旧帖子，但也许它可以帮助某人。我想在 mysql 中创建与 mongoDb 相同的数据库。
I have a valid json file with data (not structure), then I created a table in mysql with the appropriate structure (according to the json data), and imported it through MySql workbench.
我有一个包含数据（不是结构）的有效json文件，然后我在mysql中创建了一个具有适当结构（根据json数据）的表，并通过MySql工作台导入。
Hereis the example with csv, but you can load your json file. Just follow the wizard for importing data into table and make sure the structure is valid, otherwise nothing will be imported.
这是 csv 的示例，但您可以加载 json 文件。只需按照将数据导入表的向导并确保结构有效，否则将不会导入任何内容。
