PHP, MySQL Query

I am working on a project currently where I am storing a string of numbers in a users database column.

I need to do a query and find all of the users where that column contains number X.

Example:

Usesrs Tabe:

ID | NAME | CONNECTIONS
01 | Quinton | 4,44,73,91

I need to look through the users table and see which users have a connection # 44. Is there an efficient way of doing this instead of grabbing the w开发者_如何学运维hole table and then going through each users connections value.

I know there is a way to do the opposite with IN() function.

It would be better, if possible, to normalize your schema to make the string search a non-issue.

CREATE TABLE Users
(
    UserId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100)
)

CREATE TABLE UserConnections
(
    UserId INT NOT NULL REFERENCES Users (UserId),
    Connection INT NOT NULL
)

Also, index the Connection column in UserConnections. Then, search the Connection column instead. It will be much faster.

select
    *
from
    `users`
where
    concat(',', `CONNECTIONS`, ',') like concat('%,', @X, ',%')