How SELECT separate | column mysql php (1|0|2|1|0|0|1)

Question

I have column student_hobbys in my mysql table:

student_hobbys

1|0|1|1|0|0|1|0

now i try search all students who likes geography (school subject). In column student_hobbys i save this value in second argument (1|here|0|1|...). How to create a mysql query that will select students who like geography?

please help

And now you see why storing data in delimited strings in a single database column **Is such a bad idea** — RiggsFolly, Nov 24 '19 at 13:34
See: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Paul Spiegel, Nov 24 '19 at 13:35
Better start getting familiar with [MySQL String Functions and Operators](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html) — RiggsFolly, Nov 24 '19 at 13:39
Related question: [How to store 60 Booleans in a MySQL Database?](https://stackoverflow.com/questions/35394867/how-to-store-60-booleans-in-a-mysql-database) — Paul Spiegel, Nov 24 '19 at 13:40
Can't you convert this column to a bigint and use bitwise operators? https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html — Kleber, Nov 24 '19 at 15:32

Paul Spiegel · Answer 1 · 2019-11-24T14:47:04.587

Please read here why your design is a bad idea. You should store you data in a normalized way as follows:

table studens:

id | name
 1 | John
 2 | Jane
 3 | Mike
 4 | Spike

table hobbies:

id | name
 1 | biology
 2 | geogryphy
 3 | football
 4 | programming

table students_hobbies:

student_id | hobby_id
         1 | 3
         1 | 4
         2 | 1
         2 | 2
         3 | 1
         3 | 2
         3 | 4
         4 | 3

Schema definition:

CREATE TABLE students (
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(50),
  PRIMARY KEY (id),
  INDEX (name)
);

CREATE TABLE  hobbies (
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(50),
  PRIMARY KEY (id),
  INDEX (name)
);

CREATE TABLE students_hobbies (
  student_id INT UNSIGNED,
  hobby_id INT UNSIGNED,
  PRIMARY KEY (student_id, hobby_id),
  INDEX (hobby_id, student_id)
);

And your SELECT query now would be:

SELECT s.*
FROM students s
JOIN students_hobbies sh ON sh.student_id = s.id
JOIN hobbies h ON h.id = sh.hobby_id
WHERE h.name = 'geogryphy';

Result:

| id  | name |
| --- | ---- |
| 2   | Jane |
| 3   | Mike |

View on DB Fiddle

However - If you want to stick with your design, you can try something like this:

SELECT *
FROM students
WHERE student_hobbys LIKE '_|1%'

View on DB Fiddle

But it would be quite complex to generate this query programmatically. It will probably also be slower than the above solution on big dada sets, because there is no way to use an index for this kind of query.

If you want to avoid complex code in your application, you will need a quite more comlex query. One way would be to convert your string to a bitmask, and then use the bit operator & to check the bit at a specific position:

SET @hobby_position = 2;

SELECT *
FROM students
WHERE CONV(REVERSE(REPLACE(student_hobbys, '|', '')), 2, 10) & 1 << (@hobby_position - 1);

| id  | name | student_hobbys  |
| --- | ---- | --------------- |
| 2   | Jane | 1|1|1|1|0|0|1|0 |
| 3   | Mike | 1|1|1|1|0|0|1|0 |

View on DB Fiddle

There are other ways - But you will unlikely find a simple one, which can work with your design.

How SELECT separate | column mysql php (1|0|2|1|0|0|1)

1 Answers1