Mysql Query by array Data Used FIND_IN_SET

Question

table is

 CREATE TABLE `news` (
  `id` bigint(20) NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `tagkey` text NOT NULL,
  PRIMARY KEY  (`id`)
) ;

INSERT INTO `news` VALUES (1, 'TITLE1','3D');
INSERT INTO `news` VALUES (2, 'TITLE2 ','cat');
INSERT INTO `news` VALUES (3, 'TITLE2','BOOKs,3D');
INSERT INTO `news` VALUES (4, 'TITLE1','3D,DOG');
INSERT INTO `news` VALUES (5, 'TITLE2 ','DOG');
INSERT INTO `news` VALUES (6, 'TITLE2','BOOKs,DOG');
INSERT INTO `news` VALUES (7, 'TITLE1','Wolf,DOG');
INSERT INTO `news` VALUES (8, 'TITLE2 ','cat,DOG');
INSERT INTO `news` VALUES (9, 'TITLE2','BOOKs,cat');

how todo query 'cat',book . iused this query . but can't Search right data .

  select * FROM news WHERE tagkey IN ('book','cat')      
    $list = array('cat','DOG');
    $list = array_map('apply_quotes', $list);
    $query = "select * FROM news 
    WHERE tagkey IN (" . join(',', $list) . ") AND id <> $id";

You're going to have to explain what you want a little better. — andrewsi, Jun 21 '12 at 19:46
You need to normalize your database for this sort of thing. while you can use `FIND_IN_SET()` to work around this bad design, you'll be better off in the long run with a properly designed structure. — Marc B, Jun 21 '12 at 19:47
Agreed that if you can change the db structure you should. If not, you should check out [this post](http://stackoverflow.com/questions/1127088/mysql-like-in) - with a bit of work you could probably solve it using regular expressions — soupy1976, Jun 21 '12 at 20:04

Taryn · Answer 1 · 2012-06-21T20:29:12.403

3

I am going to take a wild guess that you are looking to query where something is LIKE

SELECT * 
FROM news 
WHERE tagkey LIKE '%cat%' OR tagkey LIKE '%book%'

But you should seriously consider normalizing your database and not storing a list in one column.

Or as Marc suggested in the comments FIND_IN_SET(str,strlist)

See SQL Fiddle for Demo

SELECT * 
FROM news  
WHERE FIND_IN_SET('book', tagkey)
  OR FIND_IN_SET('cat', tagkey)

edited Jun 21 '12 at 20:29

answered Jun 21 '12 at 19:58

Taryn

242,637
56
362
405

3

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set might be a bit better. – Marc B Jun 21 '12 at 19:58
1

The problem with using 'LIKE' with wildcards is that you're no longer matching the whole keyword, as in the original post. Any keyword containing 'cat' would be matched. – soupy1976 Jun 21 '12 at 20:11
@soupy1976 considering the OP doesn't provide a lot of details, I offered up a wild guess. :) – Taryn Jun 21 '12 at 20:14
1

if you are going to go this route, you probably want to ensure you match a complete entry, not just part of it. Using the LIKE operator, you'd want to prepend and append a comma, and then search for the tag between comma ... WHERE CONCAT(',',tagkey,',') LIKE '%,cat,%' OR CONCAT(',',tagkey,',') LIKE '%,book,%' – spencer7593 Jun 21 '12 at 20:19
WHERE INSTR(CONCAT(',',tagkey,','),',cat,') > 0 OR INSTR(CONCAT(',',tagkey,','),',book,') > 0 – spencer7593 Jun 21 '12 at 20:24

score 1 · Accepted Answer · answered Jun 21 '12 at 20:24

My best Answer would be DO NOT DO THAT, there's a lot of 'WHY', some of them:

You lose Performance, dependence, scalability, atomicity, Isolation, Consistency, and beside that there will be some queries simple that you'll make it more complicate, Conclusion you are doing wrong,,, but what can you do?

I will recommend you to create a relation table depending of the Cardinality and separate the items in different rows, create index, and Voila!.

Take a look to this links please, that will help you to improve your skills in the future.

Mysql Query by array Data Used FIND_IN_SET

2 Answers2