How to Use the comma Separator in the MySQL Field

The number of split fields must be limited. Therefore, it is impossible to store an infinite number of characters in a string.
The table to which this field belongs is associated with this field. It must be a one-to-many relationship.
For example, the following table structure represents the content and tag objects.
Copy codeThe Code is as follows:
Mysql> SELECT * FROM content;
+ ---- + ------ + | Id | tags | + ---- + ------ + | 1 | 1, 2 | 2, 3 | + ---- + ------ +
2 rows in set (0.01 sec)
Mysql> SELECT * FROM tag;
+ ---- + ------- + | Id | name | + ---- + ------- + | 1 | php | 2 | mysql | 3 | java | + ---- + ------- +
3 rows in set (0.00 sec)

I believe you are familiar with these principles during development. However, when you use this method to solve actual problems, you must feel a little uneasy, because this method looks more or less like a wild path. This design method is not mentioned in the thick database textbook. The standard method seems to be to use a relational ing table to insert a barrier between the two tables, although this will cause inefficient connection queries.

Every developer has struggled with standards and efficiency, but I think our efforts will make the use of this method look more standard. Note: The following methods are only available for mysql, but other databases should be portable.

Correlation search
Many developers are still using the old LIKE method for relevance retrieval. For example, in the above database structure, the two records in the content table have two tags, so how can I display the records related to its tag when I retrieve record 1. In fact, this is also a basic problem that CMS needs to face, that is, the query of related content.

If you are a cainiao, you may only think of the LIKE method. For example, first extract record 1 and then separate the tags field by commas, finally, perform a loop to use LIKE to retrieve records containing 2 in all tags fields in the content table.
Copy codeThe Code is as follows:
SELECT * FROM content WHERE tag LIKE '% 100' AND id <> 1

However, this method is too slow. If the number of queries is too large, LIKE query is a slow method. In addition, you have to deal with the problem of commas (,). In short, there is a lot of trouble.

So let's calm down and flip through the mysql manual to see if there are any surprises. At this time, a function named FIND_IN_SET will flash into your eyes. Let's take a look at the definition of this function.

Copy codeThe Code is as follows:
FIND_IN_SET (str, strlist)
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by "," characters. if the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET () function is optimized to use bit arithmetic. returns 0 if str is not in strlist or if strlist is the empty string. returns NULL if either argument is NULL. this function does not work properly if the first argument contains a comma (",") character.

Oh, PERFECT! Simply put, it is designed to find whether a string exists in another comma-separated string. Then our SQL becomes
Copy codeThe Code is as follows:
SELECT * FROM content WHERE FIND_IN_SET ('2', tags) AND id <> 1

In the process of turning over these functions, You should have deeply realized that mysql designers are certain about the storage field method separated by commas, because there are many methods designed to deal with this problem.

It looks much better, and everything seems perfect, right? Actually, no. If you have more tags, you need to create multiple SQL statements, and some records have more tags associated with each other, and some have fewer tags. How can we sort them by relevance.

At this time, you can focus on the full-text retrieval function of mysql. You must have seen countless times of this word, but it must be seldom used. Let's look at the statement directly.
Copy codeThe Code is as follows:
SELECT * FROM content where match (tags) AGAINST ('1, 2') AND id <> 1

The advantages of this statement are obvious. You do not need to split the tags field again. So what is the principle of this query? If you have a little understanding of the usage of match against, you will know that the default delimiter for full-text search is punctuation and stopwords. The former is exactly what we need. Full-text search splits the strings in MATCH and AGAINST by commas, and then matches them.

Note that the preceding SQL statement is just an example. If you execute this statement directly, you cannot get any results. The reasons are as follows:

1. You need to create a fulltext index for the tags field (if it is just a test, you can not do it. index creation only improves performance and does not affect the result)
2. Each word separated by punctuation must be at least three characters longThis is the key. If our tag id is too short, it will be automatically ignored. In this case, you can consider auto-increment IDs starting from a comparison token, such as 1000, in this way, it will be long enough.
3. You hit stopwords. For example, if your tags field is like 'hello, nobody', And the nobody is a default stop words of mysql, it will be automatically ignored. Stop words are meaningless words in English. They are not required for searching, such as auxiliary words in Chinese. But in our use, it is obviously not used for search. Therefore, you can addFt_stopword_file =''To disable it.

With the development of WEB technology, there are fewer and fewer SQL-related searches. In many cases, you only need to use a search engine. However, the purpose of this article is not only to discuss this method, but to reflect the process of achieving this result.