Does PHP MySQL select support the Explode field and sort by value?

1. For example, table A field format is as follows: Data 1| | | | | Data 2| | | | | Data 3
2. Now want to split the data into $data[0], $data [1], $data [3], then order by $data [3]
3. Can I make it?
4. Completely small white, the description may not be standardized, but the meaning should be understood.

Reply content:

1. For example, table A field format is as follows: Data 1| | | | | Data 2| | | | | Data 3
2. Now want to split the data into $data[0], $data [1], $data [3], then order by $data [3]
3. Can I make it?
4. Completely small white, the description may not be standardized, but the meaning should be understood.

Can be achieved, if implemented at the MySQL level may result in a large database pressure ratio.
Consider sequencing the PHP program and then fetching the data from MySQL based on the ID sequence.

  Query (' SELECT id,field from table ')->fetch_all (MYSQLI_ASSOC);//Suppose the data structure is extracted as follows $arr = array (' id ' = ' 1, ' field ' = ' Data 1| | | | | Data 2| | | | | , array (' id ' = 2, ' field ' = ' Data 1| | | | | Data 2| | | | | , array (' id ' = 3, ' field ' = ' Data 1| | | | | Data 2| | | | | ), foreach ($arr as $k + $v) {//Convert the contents of the field field into an array such as array (' Data 1 ', ' Data 2 ', ' 256 ') for easy ordering $arr [$k] [' field '] = Expl Ode (' | | | | | ', $v [' field ']);}    Sorts the Uasort ($arr, function ($a, $b) {if ($a [' field '][2]=== $b [' field '][2]) return 0 According to the third element in the field fields.    The following sentence means that if the previous a is greater than the B in the back, it returns 1 and returns 1 for the Exchange order, that is, the small number in front, from small to large ascending order. else return ($a [' field '][2] > $b [' field '][2]? 1:-1;}); foreach ($arr as $k = + $v) {$ids [] = $v [' id '];} $ids = Implode (', ', $ids); The ID sequence for 2,1,3//queries the MYSQL//SQL statement in order of the ID sequence for SELECT * from table WHERE ID in (2,1,3) Order by FIELD (id,2,1,3) $sql = "SELECT * FRO M table WHERE ID in ($ids) the ORDER by FIELD (ID, $ids) ";//$ret = $db->query ($sql)->fetch_all ();

If you have a large amount of data, consider caching the sequence of IDs that are queued up in PHP.
ORDER by FIELD (ID, $ids) This operation can also produce performance problems when the volume of data is large.
This can be considered according to the actual situation of $ids segmentation, such as the first page 0 to 19 sort 20, the second page and so on, so that the small amount of custom ordering for MySQL is much less pressure.

This doesn't have anything to do with PHP. You can use MySQL to create a function, and then use function to sort:

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)RETURNS VARCHAR(255)RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),       LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),       delim, '');

SELECT * FROM test_table order by SPLIT_STRING(test_tablecol, '|||', 3);

Can be implemented, cumbersome, and very inefficient, the usual solution to this requirement is to modify the table structure.

Logically speaking, SQL can actually do very complicated things, but do you think that if a query needs to run for several minutes, the family will know if they get beaten?

Recommended to use PHP after query, do not sort by MySQL

MySQL's Substring_index

