将JSON数据插入MYSQL的最佳方法 [英] Best way to Insert JSON data into MYSQL

问题描述

我在一个包含纬度和经度对的文本文件中有一个JSON数据，现在我想将这些数据存储到MYSQL数据库中，如何进行&什么是最好的方法，请建议。

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数据

[{"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 

推荐答案

有些评论建议将其保留为JSON格式并将其存储在文本列中。

Some comments have suggested leaving it in JSON format and storing it in a text column.

我建议如果你可以控制你的模式，你应该将它存储在两个 NUMERIC 字段中 - 一个用于纬度，一个用于经度。从根本上说，这就是你拥有的数据。您使用JSON作为容器格式接收它的事实与数据本身无关。

I'd suggest that if you have control over your schema, you should store it in two NUMERIC fields - 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.

如果将其存储为纬度和经度，则更简单查询它，你仍然可以在以后重新创建JSON ...但是如果你最终想要将它作为JSON获取而不是，那么你将处于更好的位置所以。这也意味着你不会将自己与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 not as 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.

我个人认为将数据的内在本质从心理上分离出来总是一个好主意你碰巧收到了它。以适合该存储的任何格式存储数据 - 因此，如果要存储数字，请使用其中一种数字数据类型。如果您要存储日期和时间，请使用 datetime 等。这样您每次都不需要在您和数据本身之间添加额外的图层访问它。

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.

这种方法也意味着你更有可能在早期发现坏数据 - 如果你只是直接存储JSON而不先解析它，你可以稍后发现纬度或经度不是有效数字。哎呀，你的样本数据有时候 lon ，有时还有 lng ，这表明你应该做一些数据清理。

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 later find that the latitude or longitude isn't a valid number. Heck, the fact that your sample data sometimes has lon and sometimes has lng suggests you should do some data cleanup anyway.

这篇关于将JSON数据插入MYSQL的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！