Mysql string field two methods to determine whether a string is contained _ MySQL

This article mainly introduces two methods for determining whether Mysql string fields contain a certain string. This article uses the Like and find_in_set methods to achieve this. For more information, see the following table:

The code is as follows:

Create table users (id int (6) not null AUTO_INCREMENT, primary key (id), user_name VARCHAR (20) not null, emails VARCHAR (50) not null );

Initialize the table and add some records.

The code is as follows:

Truncate table users
Insert into users (user_name, emails) VALUES ('login', 'A @ email.com, B @email.com, c@email.com ');
Insert into users (user_name, emails) VALUES ('king', 'AA @ email.com, bb@email.com, cc@email.com ');

　　

In Mysql, some fields are of the string type. how do I find records containing some characters?

Method 1:

The code is as follows:

SELECT * FROM users WHERE emails like "% B @email.com % ";

So the bb@email.com user also found out, not in line with expectations.

Method 2:

Use the mysql string function find_in_set ();

The code is as follows:

SELECT * FROM users WHERE find_in_set ('AA @ email.com ', emails );

　　

This is acceptable. how can this problem be solved?

Mysql has many string functions: find_in_set (str1, str2) returns the index of the position of str1 in str2. str2 must be separated by commas.

E.g.

The code is as follows:

Mysql> SELECT find_in_set () ('B', 'a, a, B, c, D') as test;
-> 3