Dwza Dwza - 5 months ago 17
MySQL Question

Use like with PDO on JSON-String

Actualy I know how to use

LIKE
statement with
PDO


Now I have a DB-Table called e.g. foobar

foobar contains a JSON-String like:

{"firstname":"foo","email":"aaa@aaa.aa","lastname":"bar"}


Now before I call my INSERT on the table I want to check if the Email is already in use.

$pdo->query("SELECT * FROM myTable WHERE foobar LIKE ?", array('%' . $email . '%'));


as you can see, I pass the email into the pdo query.
Notice
->query
is my custom function that handles some stuff. In this case its not important to know what happens.

The problem I have is:

If the entry like above exists then its not possible anymore to add an email thats like:

aa@aaa.aa
^^....only two A's


So I thought I can simply change

array('%' . $email . '%')


to

array('%"' . $email . '"%')


but this doesnt work. Is there a way I can check the whole string part ?

{"firstname":"foo","email":"aaa@aaa.aa","lastname":"bar"}

Answer

If you can't break the JSON fields into their own columns, then I would suggest a JSON column type. It's native to MySQL, super fast, and no more difficult to use than something like jq on the command line.

If that's not an option, I would use a REGEXP:

mysql> select * from foobar;
+-----------------------------------------------------------+
| json                                                      |
+-----------------------------------------------------------+
| {"firstname":"foo","email":"aaa@aaa.aa","lastname":"bar"} |
| {"firstname":"FOO","email":"aa@aaa.aa","lastname":"BAR"}  |
+-----------------------------------------------------------+

mysql> select * from foobar where json regexp '"email":"aa@aaa.aa"';
+----------------------------------------------------------+
| json                                                     |
+----------------------------------------------------------+
| {"firstname":"FOO","email":"aa@aaa.aa","lastname":"BAR"} |
+----------------------------------------------------------+
1 row in set (0.00 sec)

This is serviceable, but hardly bullet-proof and, to borrow an excellent turn of phrase, will murder performance on anything but trivial row sets.