json data in mysql

Question

I have a mysql table called "Data",

+---------+------------------+------+-----+-------------------+----------------+

| Field   | Type             | Null | Key | Default           | Extra          |
+---------+------------------+------+-----+-------------------+----------------+

| id      | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| data    | text             | YES  |     | NULL              |                |
| created | timestamp        | NO   | MUL | CURRENT_TIMESTAMP |                |
+---------+------------------+------+-----+-------------------+----------------+

The field "data" has values like this:

606 | {"first_name":"JOHN","last_name":"SLIFKO","address":"123 main AVE","city":"LAKEWOOD","state":"OH","zip":"20190","home_phone":2165216359,"email":"[email protected]",} | 2012-12-04 16:37:23 |

So, it is saving the records in a JSON Format from a PHP Script that I have.

THIS IS THE THING:

How can I structure this table to make faster searchs or consults by every single field like doing searches or queries like:

SELECT * FROM Data WHERE first_name = john;

how can I do this???

Help please......

Not sure you'll appreciate this comment but.. start by not putting Json data in MySQL... SQL databases are intended to organize data, not to store Json raw data (CouchDB is a Json oriented database, you could use it) — Ulflander
– Ulflander, Commented Dec 6, 2012 at 0:11
What Ulflander says. If you want to use mySQL's data structures, indexing, and searching, you'll need to start storing your data accordingly — Pekka
– Pekka, Commented Dec 6, 2012 at 0:12
Here is the same question, and my answer: stackoverflow.com/questions/9294836/mysql-query-on-json-data/… — JoRobles
– JoRobles, Commented Apr 10, 2013 at 18:31

calmond · Accepted Answer · 2012-12-06 00:15:49Z

1

Yikes. Not a good design. About the best you could do is use the like keyword

Select * from Data Where data like '%"first_name":"JOHN"%'

answered Dec 6, 2012 at 0:15

calmond

1897 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

json data in mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related