MySQL queries a comma-delimited field-string function Find_in_set (), and the difference between this function and the in () function

Query a comma-delimited field, you can use the String function Find_in_set ();

Query a field in a database table (the value is displayed as a branch), you can use function in ().

Today's work encountered a problem, is to use the Find_in_set () function to solve.

The first part:

Find_in_set () function usage,

Query a comma-delimited field,

The value of the Go_value field in table A is separated by commas,

The Query Go_value field contains 3 rows:

SELECT * from A where Find_in_set (' 3 ', go_value);

Part II:

In () function usage

Query a field in a database table (value branch display),

The value of the Prov field in table B is displayed as a branch,

Find the number of GID in Tianjin, Hubei province

Select COUNT (Distinct (GID)) from B where Prov in ("Hubei province", "Tianjin");

Part III:

Check the GID in Tianjin, Hubei province, but these gid are not in the a table in the ID 1 of the Go_value word Cheri (this situation, especially when a table in the ID 1 in the Go_value value is very much, or need more than Go_value value, very applicable)

Select B.gid from (select gid from B where prov in ("Hubei province", "Tianjin")) B left OUTER join
(select Go_value as GID from a where id= ' 1 ') A in Find_in_set (B.gid,a.gid) WHERE a.gid is null;

Part IV:

Additional notes:

Displays only values that are not available to the left connection query, that is, a value that shows a table exists, but does not exist in table B:

Select A.no,a.name,b.score from A left join B on a.no=b.no

where B.score is null;

MySQL queries a comma-delimited field-string function Find_in_set (), and the difference between this function and the in () function