There is such a table

CREATE TABLE localhost.`regexp` ( id int(11) NOT NULL, name varchar(50) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = INNODB AVG_ROW_LENGTH = 8192 CHARACTER SET utf8 COLLATE utf8_general_ci;

The name field stores JSON in this form.

 1|{"is_equal":["1","2","3"]} 2|{"is_equal":["3"],"is_not_equal":["2"]}

We need help to create a request for data in which the name field contains in the is_equal key a value (either 1 or 2) for example.

Conditions:

1) MySql 5.6 is not possible to work as with JSON;

2) there is no possibility to change the subd;

3) I use PHP as a server language, but I want to do it on the base side;

4) it’s not worth talking about designing tables, historically, it’s impossible to change the structure;

At the moment there are:

  SELECT * FROM localhost.`regexp` WHERE name REGEXP 'is_equal(1|2)'

but he is looking for coincidences in is_equal and in is_not_equal , and I only need that in is_equal .

  • Does the current regular expression find something? Or did you just write something to make it? - ReinRaus
  • If there is an opportunity to update MySQL to version 5.7 , you will have the opportunity to fully work with JSON . - check1st
  • Conditions: 1) MySql 5.6 is not possible to work as with JSON; - InnerFlame
  • SELECT * FROM localhost. regexp WHERE name REGEXP 'is_equal (1 | 2)' but it looks for matches in both is_equal and is_not_equal, and I only need that in is_equal - InnerFlame
  • is_equal(1|2) matches only two lines: is_equal1 and is_equal2 . Neither the first nor the second sample is not in the above JSON. This regular expression cannot match anything if you provided the correct data. Although, maybe I am mistaken in mysql some other interpretation of regular expressions, but it is unlikely. - ReinRaus

1 answer 1

to 5.7 there is an opportunity to work with json with the help of udf files that Sveta Smirnova wrote http://www.slideshare.net/SvetaSmirnova/json-ud-fsru

  • something new, thanks - InnerFlame