Java 将 JSON 数据插入 MYSQL 的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31130385/
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
Best way to Insert JSON data into MYSQL
提问by Rakesh
I have a JSON data in a text file which contains pair of latitude and longitude,Now I want to store this data into MYSQL DB,How to go about & what's the best approach please suggest.
我在一个包含纬度和经度对的文本文件中有一个 JSON 数据，现在我想将此数据存储到 MYSQL DB 中，如何去做以及最好的方法是什么，请提出建议。
Here is My JSON data
这是我的 JSON 数据
[{"latlon":{"lng":77.75124312,"lat":12.97123123},"type":"s"},
{"latlon":{"lon":77.73004942455374,"lat":12.98227579706589},"type":"s"},
{"latlon":{"lon":77.67374449291539,"lat":12.995490063545521},"type":"v"},
{"latlon":{"lon":77.6097147993144,"lat":12.970900929013666},"type":"s"},
{"latlon":{"lon":77.53933363476645,"lat":12.948316929346504},"type":"s"},
{"latlon":{"lng":77.48213123,"lat":12.91213213},"type":"s"}
.
.
.
.
]
The String may go up to 50 points
采纳答案by Jon Skeet
Some comments have suggested leaving it in JSON format and storing it in a text column.
一些评论建议将其保留为 JSON 格式并将其存储在文本列中。
I'd suggest that if you have control over your schema, you should store it in two NUMERICfields - one for latitude, and one for longitude. Fundamentally, that's the data you have. The fact that you've received it using JSON as a container format is irrelevant to the data itself.
我建议，如果您可以控制架构，则应将其存储在两个NUMERIC字段中 - 一个用于纬度，一个用于经度。从根本上说，这就是您拥有的数据。您使用 JSON 作为容器格式接收它这一事实与数据本身无关。
If you store it as a latitude and a longitude, it's simpler to query over it, and you can still recreate the JSON later if you want to... but if you end up wanting to fetch it notas JSON, you'll be in a much better position to do so. It also means you're not tying yourself to JSON against future changes.
如果您将其保存为纬度和经度，它结束了更简单的查询，你仍然可以在以后重新创建JSON，如果你想......但如果你最终想要把它取不为JSON，你会处于更好的位置来这样做。这也意味着您不会将自己与 JSON 捆绑在一起以应对未来的变化。
Personally I think it's always a good idea to mentally separate the intrinsic nature of the data from the way that you happen to receive it. Store the data in whatever format is idiomatic for that storage - so if you're storing a number, use one of the numeric data types. If you're storing a date and time, use datetime, etc. That way you don't end up with an extra layer between you and the data itself every time you want to access it.
就我个人而言，我认为将数据的内在本质与您碰巧接收它的方式在精神上分开总是一个好主意。以该存储惯用的任何格式存储数据 - 因此，如果您要存储数字，请使用其中一种数字数据类型。如果您要存储日期和时间，请使用datetime等。这样，每次您想访问数据时，您和数据本身之间就不会出现额外的层。
This approach also means you're much more likely to spot bad data early on - if you just store the JSON directly without parsing it first, you could laterfind that the latitude or longitude isn't a valid number. Heck, the fact that your sample data sometimes has lonand sometimes has lngsuggests you should do some data cleanup anyway.
这种方法还意味着您更有可能在早期发现错误数据 - 如果您只是直接存储 JSON 而没有先对其进行解析，您稍后可能会发现纬度或经度不是有效数字。哎呀，您的样本数据有时有lon，有时有，这一事实lng表明您无论如何都应该进行一些数据清理。
回答by Shekhar Khairnar
This may help you :-
这可能对您有所帮助：-
Check out following site it convert your josn data file to sql insert script:
查看以下站点，它将您的 josn 数据文件转换为 sql 插入脚本：
回答by Avinash Sahu
If you are using Java for application you can do something like this.
如果您将 Java 用于应用程序，您可以执行以下操作。
Convert JSONObject into String and save as TEXT/ VARCHAR. While retrieving the same column convert the String into JSONObject.
将 JSONObject 转换为 String 并另存为 TEXT/VARCHAR。在检索同一列时，将 String 转换为 JSONObject。
For example
例如
Write into DB
写入数据库
String stringToBeInserted = jsonObject.toString();
//and insert this string into DB
Read from DB
从数据库读取
String json = Read_column_value_logic_here
JSONObject jsonObject = new JSONObject(json);
回答by Mathieu
Basically, if that's a data that matters to your software (if there will be queries involving those data), you should considere parsing the JSON.
基本上，如果这是对您的软件很重要的数据（如果将有涉及这些数据的查询），您应该考虑解析 JSON。
If not : store it as it is !
如果不是：按原样存储！
回答by adnan
From Mysql 5.7.10 and higher JSON support is native so you can have a JSON data type
从 Mysql 5.7.10 和更高版本开始，JSON 支持是原生的，因此您可以拥有 JSON 数据类型
Document Validation- Only valid JSON documents can be stored in a JSON column, so you get automatic validation of your data.
Efficient Access- More importantly, when you store a JSON document in a JSON column, it is not stored as a plain text value. Instead, it is stored in an optimized binary format that allows for quicker access to object members and array elements.
Performance- Improve your query performance by creating indexes on values within the JSON columns. This can be achieved with “functional indexes” on virtual columns.
Convenience- The additional inline syntax for JSON columns makes it very natural to integrate Document queries within your SQL. For example (features.feature is a JSON column):
SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;
文档验证- 只有有效的 JSON 文档才能存储在 JSON 列中，因此您可以自动验证数据。
高效访问- 更重要的是，当您将 JSON 文档存储在 JSON 列中时，它不会存储为纯文本值。相反，它以优化的二进制格式存储，允许更快地访问对象成员和数组元素。
性能- 通过为 JSON 列中的值创建索引来提高查询性能。这可以通过虚拟列上的“功能索引”来实现。
方便- JSON 列的附加内联语法使得在 SQL 中集成文档查询变得非常自然。例如（features.feature 是一个 JSON 列）：
SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;
