Exact MYSQL search and PHP Array Operations

The TAG field in mysql contains the following information: {code ...} I hope to use mysql to search for all non-repeated tags at a time, like this result {code ...} if a search fails, perform the following operations on the PHP array as simple as possible.

phpphp,mysqljqueryhtmlphprubyjava,jquery,jsjavahtmlcss

I want to use mysql to search all non-repeated tags at a time, just like this result.

phpmysqljqueryhtmlrubyjavacss

If a search fails, you can use the PHP array as simple as possible.

Reply content:

The TAG field in mysql contains the following conditions:

phpphp,mysqljqueryhtmlphprubyjava,jquery,jsjavahtmlcss

I want to use mysql to search all non-repeated tags at a time, just like this result.

phpmysqljqueryhtmlrubyjavacss

If a search fails, you can use the PHP array as simple as possible.

A successful SQL operation seems a little difficult for me. My thoughts are:

• Read data honestly first

code:    SELECT * FROM tagresult example:    $result = array('php','php,mysql','jquery','html','php','ruby','java,jquery,js','java','html','css');

• ExploitationImplodeFunction concatenates an array into a string (the string used for the connection is,)

code:    $result = implode(',',$result);result example:    $result = 'php,php,mysql,jquery,html,php,ruby,java,jquery,js,java,html,css';

• ExploitationExplodeThe function truncates a string into an array.,)

code:    $result = explode(',',$result);result example:    $result = Array ( [0] => php [1] => php [2] => mysql [3] => jquery [4] => html [5] => php [6] => ruby [7] => java [8] => jquery [9] => js [10] => java [11] => html [12] => css );

• Final ExploitationArray_uniqueThe function removes repeated values.

code:    $result = array_unique($result);result example:    $result = Array ( [0] => php [2] => mysql [3] => jquery [4] => html [6] => ruby [7] => java [9] => js [12] => css )

My understanding is that some records in your table have tags separated by commas (,), but you want to query tags separated by commas (,) as in the same way as independent records when querying SQL statements, and deduplication.
I will explain my ideas about how to handle this problem in SQL. Of course, if the data volume is large, the performance will be poor, because too many string operations are involved, and mysql itself does not support split. If you use an SQL statement to find out the rule, you must first summarize the rule of your data. For example, the maximum number of tags separated by "," is contained in your record, the following code assumes that based on your data:

phpphp,mysqljqueryhtmlphprubyjava,jquery,jsjavahtmlcss

A record contains up to three tags and two commas. Use the code to create the scenario data you mentioned:

/*!40101 SET NAMES utf8 */;create table `tags` (`tag` varchar (150)); insert into `tags` (`tag`) values('php');insert into `tags` (`tag`) values('php,mysql');insert into `tags` (`tag`) values('jquery');insert into `tags` (`tag`) values('html');insert into `tags` (`tag`) values('php');insert into `tags` (`tag`) values('ruby');insert into `tags` (`tag`) values('java,jquery,js');insert into `tags` (`tag`) values('java');insert into `tags` (`tag`) values('html');insert into `tags` (`tag`) values('css');

Run the following SQL query:

SELECT DISTINCT tag FROM (SELECT DISTINCT tag  FROM tags WHERE tag  NOT LIKE '%,%'UNIONSELECT DISTINCT SUBSTRING_INDEX(tag , ',', 1) AS tag  FROM tags WHERE tag  LIKE '%,%'UNIONSELECT DISTINCT SUBSTRING_INDEX(SUBSTRING(tag ,INSTR(tag ,',')+1),',', 1) AS tag  FROM tags WHERE SUBSTRING(tag ,INSTR(tag ,',')+1)  LIKE '%,%'UNIONSELECT DISTINCT SUBSTRING_INDEX(tag , ',', -1) AS tag  FROM tags WHERE tag  LIKE '%,%') t

The result is as follows:

tagphpjqueryhtmlrubyjavacssmysqljs

The method is very stupid. The results are obtained through four queries and then merged and de-duplicated, respectively

1. Query all tags in records without commas (,) and deduplicate them (deduplication is used to reduce the data volume during merging );
2. Query the first tag in a record separated by commas (,) and then deduplicate it;
3. Query the second tag of a record separated by commas (,) and then deduplicate it;
4. Query the third tag in a record separated by commas (,) and then deduplicate it;
5. Merge all the preceding records and deduplicate them again. The result is displayed;

A composite SQL statement is returned. Of course, this SQL statement has poor scalability and performance. If your data format is changed or even the number of commas in a single record is greater, this SQL is game over. We recommend that you use sp to achieve dynamic implementation, so that you can better adapt to the growth of tag size in a single record. Otherwise, as I did above, a single comma would crash. If you are doing this in php or java, I believe it is much more convenient.

Why not use group