MySQL에 데이터를 JSON으로 저장
나는 이것이 n00b 일이라고 생각했다. 그리고, 나는 그것을 한 적이 없습니다. 그런 다음 FriendFeed가이 작업을 수행하여 실제로 DB 규모를 개선하고 지연 시간을 줄인 것을 알았습니다. 이 작업을 수행해야하는지 궁금합니다. 그렇다면 그렇다면 올바른 방법은 무엇입니까?
기본적으로 MySQL에 모든 것을 CouchDB 종류의 DB로 저장하는 방법을 배우기에 좋은 곳은 무엇입니까? JSON으로 모든 것을 저장하는 것이 쉽고 빠릅니다 (빌드하지 않고 대기 시간을 줄임).
또한 DB에 JSON으로 저장된 항목을 쉽게 편집, 삭제하는 등의 작업이 가능합니까?
CouchDB와 MySQL은 매우 다른 두 가지 짐승입니다. JSON은 CouchDB에 물건을 저장하는 기본 방법입니다. MySQL에서는 JSON 데이터를 단일 필드에 텍스트로 저장하는 것이 가장 좋습니다. 이는 RDBMS에 저장하려는 목적을 완전히 상실하고 모든 데이터베이스 트랜잭션을 크게 복잡하게 만듭니다.
하지마
그러나 FriendFeed 는 MySQL 위에 매우 맞춤 스키마 를 사용하는 것처럼 보였습니다 . 실제로 저장하려는 대상에 따라 다르며 데이터베이스 시스템을 악용하는 방법에 대한 명확한 대답은 거의 없으므로 귀하에게 적합합니다. 이 기사가 매우 오래되어 Mongo와 Couch에 대한 주된 이유가 미숙했기 때문에 MySQL이 당신을 위해 그것을 자르지 않으면이 두 가지를 다시 평가할 것입니다. 그들은 지금까지 많이 성장했을 것입니다.
모든 사람의 의견이 잘못된 각도에서 오는 것 같습니다 .PHP를 통해 JSON을 관계형 DB에 저장하는 것이 좋습니다. 실제로 이와 같은 복잡한 데이터를로드하고 표시하는 것이 더 빠를 것입니다. 그러나 다음과 같은 디자인 고려 사항이 있습니다. 검색, 색인 생성 등
이 작업을 수행하는 가장 좋은 방법은 하이브리드 데이터를 사용하는 것입니다. 예를 들어 날짜 시간을 기준으로 검색해야하는 경우 MySQL (성능 조정)이 PHP보다 훨씬 빠르며 검색 거리와 같은 경우 MySQL도 많이 있어야합니다. 더 빠르게 (검색에 액세스하지 않음). 검색 할 필요가없는 데이터는 JSON, BLOB 또는 실제로 필요한 다른 형식으로 저장할 수 있습니다.
액세스해야하는 데이터는 기본 사례 별 송장 시스템과 같이 JSON으로 매우 쉽게 저장됩니다. RDBMS에서 큰 이점을 얻지 못하고 올바른 HTML 양식 구조가 있으면 json_encoding ($ _ POST [ 'entires'])만으로 JSON에 저장할 수 있습니다.
MongoDB를 사용하게되어 기쁘고 계속해서 계속 서비스를 제공하기를 희망하지만 MySQL이 항상 레이더에서 벗어날 것이라고 생각하지 마십시오. 앱의 복잡성이 증가함에 따라 RDBMS가 필요할 수 있습니다. 일부 기능 및 특징 (아카이브 된 데이터 또는 비즈니스보고 만 폐기하더라도)
MySQL 5.7은 이제 MongoDB 및 기타 스키마없는 문서 데이터 저장소와 유사한 기본 JSON 데이터 유형을 지원합니다.
JSON 지원
MySQL 5.7.8부터 MySQL은 기본 JSON 유형을 지원합니다. JSON 값은 문서 요소에 대한 빠른 읽기 액세스를 허용하는 내부 이진 형식을 사용하는 대신 문자열로 저장되지 않습니다. JSON 열에 저장된 JSON 문서는 삽입되거나 업데이트 될 때마다 자동으로 유효성이 검사되며 오류가 발생하는 잘못된 문서가 있습니다. JSON 문서는 생성시 정규화되며 =, <, <=,>,> =, <>,! = 및 <=>와 같은 대부분의 비교 연산자를 사용하여 비교할 수 있습니다. JSON 값을 비교할 때 MySQL이 따르는 우선 순위 및 기타 규칙뿐만 아니라 지원되는 연산자에 대한 자세한 내용은 JSON 값 비교 및 순서를 참조하십시오.
MySQL 5.7.8은 또한 JSON 값으로 작업하기위한 많은 함수를 소개합니다. 이러한 기능에는 다음과 같은 기능이 포함됩니다.
- JSON 값을 작성하는 함수 : JSON_ARRAY (), JSON_MERGE () 및 JSON_OBJECT () 12.16.2 절“JSON 값을 생성하는 기능”을 참조하십시오.
- JSON 값을 검색하는 함수 : JSON_CONTAINS (), JSON_CONTAINS_PATH (), JSON_EXTRACT (), JSON_KEYS () 및 JSON_SEARCH (). 12.16.3 절“JSON 값을 검색하는 기능”을 참조하십시오.
- JSON 값을 수정하는 함수 : JSON_APPEND (), JSON_ARRAY_APPEND (), JSON_ARRAY_INSERT (), JSON_INSERT (), JSON_QUOTE (), JSON_REMOVE (), JSON_REPLACE (), JSON_SET () 및 JSON_UNQUOTE () 12.16.4 절“JSON 값을 수정하는 기능”을 참조하십시오.
- JSON 값에 대한 정보를 제공하는 함수 : JSON_DEPTH (), JSON_LENGTH (), JSON_TYPE () 및 JSON_VALID (). 12.16.5 절“JSON 값 속성을 반환하는 함수”를 참조하십시오.
MySQL 5.7.9 이상에서는 column-> path를 JSON_EXTRACT (열, 경로)의 속기로 사용할 수 있습니다. 이는 WHERE, ORDER BY 및 GROUP BY 절을 포함하여 SQL 문에서 컬럼 ID가 발생할 수있는 경우 컬럼의 별명으로 작동합니다. 여기에는 SELECT, UPDATE, DELETE, CREATE TABLE 및 기타 SQL 문이 포함됩니다. 왼쪽은 별칭이 아닌 JSON 열 식별자 여야합니다. 오른쪽은 인용 된 JSON 경로 표현식으로, 열 값으로 리턴 된 JSON 문서와 비교하여 평가됩니다.
-> 및 JSON_EXTRACT ()에 대한 자세한 내용은 12.16.3 절“JSON 값을 검색하는 기능”을 참조하십시오. MySQL 5.7의 JSON 경로 지원에 대한 자세한 내용은 JSON 값 검색 및 수정을 참조하십시오. 보조 인덱스 및 가상 생성 열도 참조하십시오.
더 많은 정보:
https://dev.mysql.com/doc/refman/5.7/en/json.html
JSON 문자는 스토리지와 관련하여 특별한 것이 없습니다.
{, }, [, ], ', a-z, 0-9.... 정말 특별한 아무것도 및 텍스트로 저장할 수 있습니다.
당신이 가질 첫 번째 문제는 이것입니다
{profile_id : 22, 사용자 이름 : 'Robert', 비밀번호 : 'skhgeeht893htgn34ythg9er'}
자신의 절차를 따르지 않고 mysql 용 jsondecode를 개발하지 않으면 데이터베이스에 저장된 것이 업데이트하기가 쉽지 않습니다.
UPDATE users SET JSON(user_data,'username') = 'New User';
그래서 당신이 먼저 json을 선택하고, 그것을 해독하고, 변경하고, 업데이트해야하므로 이론적으로는 적절한 데이터베이스 구조를 만드는 데 더 많은 시간을 할애 할 수 있습니다!
json을 사용하여 데이터를 저장하지만 메타 데이터, 자주 업데이트되지 않는 데이터, 사용자와 관련이없는 데이터 만 사용합니다. 예를 들어 사용자가 게시물을 추가하면 해당 게시물에서 이미지를 구문 분석하고 엄지 손가락을 만들고 그런 다음 엄지 손가락 URL을 json 형식으로 사용하십시오.
쿼리를 사용하여 JSON 데이터를 얻는 것이 얼마나 어려운지를 설명하기 위해이를 처리하기 위해 만든 쿼리를 공유하겠습니다.
배열이나 다른 객체는 고려하지 않고 기본 데이터 유형 만 고려합니다. 4 개의 열 인스턴스를 JSON을 저장하는 열 이름으로 변경하고 myfield 의 4 개의 인스턴스 를 액세스하려는 JSON 필드로 변경 해야합니다.
SELECT
SUBSTRING(
REPLACE(REPLACE(REPLACE(column, '{', ''), '}', ','), '"', ''),
LOCATE(
CONCAT('myfield', ':'),
REPLACE(REPLACE(REPLACE(column, '{', ''), '}', ','), '"', '')
) + CHAR_LENGTH(CONCAT('myfield', ':')),
LOCATE(
',',
SUBSTRING(
REPLACE(REPLACE(REPLACE(column, '{', ''), '}', ','), '"', ''),
LOCATE(
CONCAT('myfield', ':'),
REPLACE(REPLACE(REPLACE(column, '{', ''), '}', ','), '"', '')
) + CHAR_LENGTH(CONCAT('myfield', ':'))
)
) - 1
)
AS myfield
FROM mytable WHERE id = '3435'
It really depends on your use case. If you are storing information that has absolutely no value in reporting, and won't be queried via JOINs with other tables, it may make sense for you to store your data in a single text field, encoded as JSON.
This could greatly simplify your data model. However, as mentioned by RobertPitt, don't expect to be able to combine this data with other data that has been normalized.
This is an old question, but I am still able to see this at the top of the search result of Google, so I guess it would be meaningful to add a new answer 4 years after the question is asked.
First of all, there is better support in storing JSON in RDBMS. You may consider switching to PostgreSQL (although MySQL has supported JSON since v5.7.7). PostgreSQL uses very similar SQL commands as MySQL except they support more functions. One of the functions they added is that they provide JSON data type and you are now able to query the JSON stored. (Some reference on this) If you are not making up the query directly in your program, for example, using PDO in php or eloquent in Laravel, all you need to do is just to install PostgreSQL on your server and change database connection settings. You don't even need to change your code.
Most of the time, as the other answers suggested, storing data as JSON directly in RDBMS is not a good idea. There are some exception though. One situation I can think of is a field with variable number of linked entry.
For example, for storing tag of a blog post, normally you will need to have a table for blog post, a table of tag and a matching table. So, when the user wants to edit a post and you need to display which tag is related to that post, you will need to query 3 tables. This will damage the performance a lot if your matching table / tag table is long.
By storing the tags as JSON in the blog post table, the same action only requires a single table search. The user will then be able to see the blog post to be edit quicker, but this will damage the performance if you want to make a report on what post is linked to a tag, or maybe search by tag.
You may also try to de-normalize the database. By duplicating the data and storing the data in both ways, you can receive benefit of both method. You will just need a little bit more time to store your data and more storage space (which is cheap comparing to the cost of more computing power)
I would say the only two reasons to consider this are:
- performance just isn't good enough with a normalised approach
- you cannot readily model your particularly fluid/flexible/changing data
I wrote a bit about my own approach here:
What scalability problems have you encountered using a NoSQL data store?
(see the top answer)
Even JSON wasn't quite fast enough so we used a custom-text-format approach. Worked / continues to work well for us.
Is there a reason you're not using something like MongoDB? (could be MySQL is "required"; just curious)
It seems to me that everyone answering this question is kind-of missing the one critical issue, except @deceze -- use the right tool for the job. You can force a relational database to store almost any type of data and you can force Mongo to handle relational data, but at what cost? You end up introducing complexity at all levels of development and maintenance, from schema design to application code; not to mention the performance hit.
In 2014 we have access to many database servers that handle specific types of data exceptionally well.
- Mongo (document storage)
- Redis (key-value data storage)
- MySQL/Maria/PostgreSQL/Oracle/etc (relational data)
- CouchDB (JSON)
I'm sure I missed some others, like RabbirMQ and Cassandra. My point is, use the right tool for the data you need to store.
If your application requires storage and retrieval of a variety of data really, really fast, (and who doesn't) don't shy away from using multiple data sources for an application. Most popular web frameworks provide support for multiple data sources (Rails, Django, Grails, Cake, Zend, etc). This strategy limits the complexity to one specific area of the application, the ORM or the application's data source interface.
Here is a function that would save/update keys of a JSON array in a column and another function that retrieves JSON values. This functions are created assuming that the column name of storing the JSON array is json. It is using PDO.
Save/Update Function
function save($uid, $key, $val){
global $dbh; // The PDO object
$sql = $dbh->prepare("SELECT `json` FROM users WHERE `id`=?");
$sql->execute(array($uid));
$data = $sql->fetch();
$arr = json_decode($data['json'],true);
$arr[$key] = $val; // Update the value
$sql=$dbh->prepare("UPDATE `users` SET `json`=? WHERE `id`=?");
$sql->execute(array(
json_encode($arr),
$uid
));
}
where $uid is the user's id, $key - the JSON key to update and it's value is mentioned as $val.
Get Value Function
function get($uid, $key){
global $dbh;
$sql = $dbh->prepare("SELECT `json` FROM `users` WHERE `id`=?");
$sql->execute(array($uid));
$data = $sql->fetch();
$arr = json_decode($data['json'], true);
return $arr[$key];
}
where $key is a key of JSON array from which we need the value.
I believe that storing JSON in a mysql database does in fact defeat the purpose of using RDBMS as it is intended to be used. I would not use it in any data that would be manipulated at some point or reported on, since it not only adds complexity but also could easily impact performance depending on how it is used.
However, I was curious if anyone else thought of a possible reason to actually do this. I was thinking to make an exception for logging purposes. In my case, I want to log requests that have a variable amount of parameters and errors. In this situation, I want to use tables for the type of requests, and the requests themselves with a JSON string of different values that were obtained.
In the above situation, the requests are logged and never manipulated or indexed within the JSON string field. HOWEVER, in a more complex environment, I would probably try to use something that has more of an intention for this type of data and store it with that system. As others have said, it really depends on what you are trying to accomplish, but following standards always helps longevity and reliability!
Early support for storing JSON in MySQL has been added to the MySQL 5.7.7 JSON labs release (linux binaries, source)! The release seems to have grown from a series of JSON-related user-defined functions made public back in 2013.
This nascent native JSON support seems to be heading in a very positive direction, including JSON validation on INSERT, an optimized binary storage format including a lookup table in the preamble that allows the JSN_EXTRACT function to perform binary lookups rather than parsing on every access. There is also a whole raft of new functions for handling and querying specific JSON datatypes:
CREATE TABLE users (id INT, preferences JSON);
INSERT INTO users VALUES (1, JSN_OBJECT('showSideBar', true, 'fontSize', 12));
SELECT JSN_EXTRACT(preferences, '$.showSideBar') from users;
+--------------------------------------------------+
| id | JSN_EXTRACT(preferences, '$.showSideBar') |
+--------------------------------------------------+
| 1 | true |
+--------------------------------------------------+
IMHO, the above is a great use case for this new functionality; many SQL databases already have a user table and, rather than making endless schema changes to accommodate an evolving set of user preferences, having a single JSON column a single JOIN away is perfect. Especially as it's unlikely that it would ever need to be queried for individual items.
While it's still early days, the MySQL server team are doing a great job of communicating the changes on the blog.
JSON is a valid datatype in PostgreSQL database as well. However, MySQL database has not officially supported JSON yet. But it's baking: http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/
I also agree that there are many valid cases that some data is better be serialized to a string in a database. The primary reason might be when it's not regularly queried, and when it's own schema might change - you don't want to change the database schema corresponding to that. The second reason is when the serialized string is directly from external sources, you may not want to parse all of them and feed in the database at any cost until you use any. So I'll be waiting for the new MySQL release to support JSON since it'll be easier for switching between different database then.
I use json to record anything for a project, I use three tables in fact ! one for the data in json, one for the index of each metadata of the json structure (each meta is encoded by an unique id), and one for the session user, that's all. The benchmark cannot be quantified at this early state of code, but for exemple I was user views (inner join with index) to get a category (or anything, as user, ...), and it was very slow (very very slow, used view in mysql is not the good way). The search module, in this structure, can do anything I want, but, I think mongodb will be more efficient in this concept of full json data record. For my exemple, I user views to create tree of category, and breadcrumb, my god ! so many query to do ! apache itself gone ! and, in fact, for this little website, I use know a php who generate tree and breadcrumb, the extraction of the datas is done by the search module (who use only index), the data table is used only for update. If I want, I can destroy the all indexes, and regenerate it with each data, and do the reverse work to, like, destroy all the data (json) and regenerate it only with the index table. My project is young, running under php and mysql, but, sometime I thing using node js and mongodb will be more efficient for this project.
Use json if you think you can do, just for do it, because you can ! and, forget it if it was a mistake; try by make good or bad choice, but try !
Low
a french user
I know this is really late but I did have a similar situation where I used a hybrid approach of maintaining RDBMS standards of normalizing tables upto a point and then storing data in JSON as text value beyond that point. So for example I store data in 4 tables following RDBMS rules of normalization. However in the 4th table to accomodate dynamic schema I store data in JSON format. Every time I want to retrieve data I retrieve the JSON data, parse it and display it in Java. This has worked for me so far and to ensure that I am still able to index the fields I transform to json data in the table to a normalized manner using an ETL. This ensures that while the user is working on the application he faces minimal lag and the fields are transformed to a RDBMS friendly format for data analysis etc. I see this approach working well and believe that given MYSQL (5.7+) also allows parsing of JSON this approach gives you the benefits of both RDBMS and NOSQL databases.
You can use this gist: https://gist.github.com/AminaG/33d90cb99c26298c48f670b8ffac39c3
After installing it to the server (just need root privilege not super), you can do something like this:
select extract_json_value('{"a":["a","2"]}','(/a)')
It will return a 2 .You can return anything inside JSON by using this The good part is that it is support MySQL 5.1,5.2,5.6. And you do not need to install any binary on the server.
Based on old project common-schema, but it is still working today https://code.google.com/archive/p/common-schema/
참고URL : https://stackoverflow.com/questions/3564024/storing-data-in-mysql-as-json
'program story' 카테고리의 다른 글
| 도커-컨테이너에서 자동 다시 시작을 어떻게 비활성화합니까? (0) | 2020.07.26 |
|---|---|
| LINQ에서 날짜가 가장 높은 레코드 만 선택하는 방법 (0) | 2020.07.26 |
| 제출시 양식 새로 고침 중지 페이지 (0) | 2020.07.26 |
| jQuery 아약스 오류 함수 (0) | 2020.07.26 |
| 로그인 페이지로의 각도 재 지정 (0) | 2020.07.26 |