StringInString Query?

I have this database where I have stored some tags in it. I stored the tags like this:

"humor,funny,animal"

Now I need a mysql query that selects this line when I search for "humor", "funny" or "animal". What I have until开发者_如何转开发 now:

SELECT id FROM database WHERE tags REGEXP 'humor' LIMIT 1

Unfortunately, it does not work. Could someone of you please help me out?

Edit: Thanks for all the responses! I will now need to study this first! But problem solved :)

Short Term

Because the tags are stored as denormalized data, use the FIND_IN_SET function:

SELECT t.id
  FROM YOUR_TABLE t
 WHERE FIND_IN_SET('humour', t.tags) > 0

Long Term Solution

Setup the tables to properly handle a many-to-many relationship:

TAGS

• tag_id (primary key)
• tag_description

ITEMS

• item_id (primary key)

ITEM_TAGS

• item_id (primary key, foreign key to ITEMS.item_id)
• tag_id (primary key, foreign key to TAGS.tag_id)

Making the two columns in ITEM_TAGS the primary key means you don't have to worry about duplicates. And yes, this means using the InnoDB engine...

Then, you can use:

SELECT i.item_id
  FROM ITEMS i
 WHERE EXISTS (SELECT NULL
                 FROM ITEM_TAGS it
                 JOIN TAGS t ON t.tag_id = it.tag_id
                WHERE t.tag_description = 'humour'
                  AND it.item_id = i.item_id)

You can use LIKE

 SELECT id FROM database WHERE tags LIKE '%humor%' LIMIT 1

Which will search for any entry where 'humor' is a substring. Note this will also return items tagged 'humorous'.

But like others said, having a separate table for tags would be best. To do this you will also need a pivot table.

So for example

--------------  data  -------------
|     ID         |     NAME       | 
| 1              | example        |
| 2              | example 2      | 
-----------------------------------

--------------  tags  -------------
|      ID        |     NAME       |
| 1              | humor          |
| 2              | cats           |
| 3              | wumpus         |
-----------------------------------

------------  data_tags  ----------
|   DATA_ID      |   TAG_ID       |
| 1              | 1              |
| 1              | 2              |
| 2              | 1              |
| 2              | 3              |
-----------------------------------

To expand on Tomalak's comment, this would be best solved using a many-to-many relationship for database to tag relationships. This involves adding two new tables. Something like this (forgive my rusty MySQL)

CREATE TABLE `Tag` (
    id  INT(11)     UNSIGNED NOT NULL AUTO_INCREMENT,
    tag VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE      (tag)
) ENGINE=InnoDB;

CREATE TABLE `DatabaseTag` (
    database_id INT(11) UNSIGNED NOT NULL, -- just guessing your database.id type here
    tag_id      INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (database_id, tag_id),
    FOREIGN KEY (database_id) REFERENCES `database` (id)
        ON DELETE CASCADE,
    FOREIGN KEY (tag_id)      REFERENCES `Tag` (id)
        ON DELETE CASCADE
) ENGINE=InndoDB;

Then, to find all the database records matching tag "humour", your query would look like

SELECT id FROM `database` d
INNER JOIN `DatabaseTag` dt ON d.id = dt.database_id
INNER JOIN `Tag` t ON dt.tag_id = t.id
WHERE t.tag = 'humour'