Suppose you have a table:
The code is as follows:
CREATE TABLE users (id int (6) Not NULL auto_increment,primary KEY (ID), user_name VARCHAR (m) not null,emails VARCHAR (x) T NULL);
Initialize the table and add some records.
The code is as follows:
TRUNCATE TABLE Users
INSERT into users (user_name, emails) VALUES (' Xiao Zhang ', ' a@email.com,b@email.com,c@email.com ');
INSERT into users (user_name, emails) VALUES (' Xiao Wang ', ' aa@email.com,bb@email.com,cc@email.com ');
Some of the fields in Mysql are string types, how do you find records that contain certain characters?
Method One:
The code is as follows:
SELECT * from the users WHERE emails like "%b@email.com%";
So bb@email.com users also found out, not in line with expectations.
Method Two:
Using MySQL string function find_in_set ();
The code is as follows:
SELECT * from Users WHERE find_in_set (' aa@email.com ', emails);
This is possible, how to understand it?
MySQL has a lot of string functions the Find_in_set (STR1,STR2) function is the index of the location where STR1 is returned in str2, and str2 must be split by "."
e.g.
The code is as follows:
MySQL > SELECT find_in_set () (' B ', ' a,a,b,c,d ') as test;
-> 3