Does php mysql select support explode fields and then sort according to values?

1. For example, the format of a field in the table is as follows: data 1|||||data 2|||||data 3
2. Now I want to split the data into $data[0],$data[1],$ data[3], and then order by $data[3]
3. Can this be achieved?
4. A complete novice, the description may not be standardized, but the meaning should be clear.

Reply content:

1. For example, the format of a field in the table is as follows: data 1|||||data 2|||||data 3
2. Now I want to split the data into $data[0],$data[1],$ data[3], and then order by $data[3]
3. Can this be achieved?
4. A complete novice, the description may not be standardized, but the meaning should be clear.

It can be implemented, but if implemented at the MySQL level, it may cause greater pressure on the database.
You can consider sorting it in the PHP program, and then fetching the data from MySQL based on the ID sequence.

<code><?php header('Content-Type: text/plain; charset=utf-8');
//$db = new mysqli('127.0.0.1','user','pass','dbname',3306);
//$arr = $db->query('SELECT id,field FROM table')->fetch_all(MYSQLI_ASSOC);
//假设取出来的数据结构如下
$arr = array(
    array('id' => 1, 'field' => '数据1|||||数据2|||||256'),
    array('id' => 2, 'field' => '数据1|||||数据2|||||128'),
    array('id' => 3, 'field' => '数据1|||||数据2|||||512'),
);
foreach($arr as $k => $v) {
    //把field字段的内容转成一个数组比如array('数据1','数据2','256'),方便后面排序
    $arr[$k]['field'] = explode('|||||', $v['field']);
}
//根据field字段里的第三个元素对数组进行排序
uasort($arr, function($a, $b) {
    if($a['field'][2]===$b['field'][2]) return 0;
    //下面这句话的意思就是,如果前面的a大过后面的b,就返回1,返回1表示交换顺序,也就是小的数在前面,由小到大升序排列.
    else return ($a['field'][2] > $b['field'][2]) ? 1 : -1;
});
foreach($arr as $k => $v) {
    $ids[] = $v['id'];
}
$ids = implode(',', $ids); //ID序列为2,1,3
//按照ID序列的顺序查询MySQL
//SQL语句为 SELECT * FROM table WHERE id IN(2,1,3) ORDER BY FIELD(id,2,1,3)
$sql = "SELECT * FROM table WHERE id IN($ids) ORDER BY FIELD(id,$ids)";
//$ret = $db->query($sql)->fetch_all();</code>

If the amount of data is large, you can consider caching the ID sequence arranged by PHP.
ORDER BY FIELD (id, $ids) This operation will also cause performance problems when the amount of data is large.
At this time, you can consider caching the ID sequence based on the actual situation. Segment $ids, for example, sort 20 items from 0 to 19 on the first page, and so on on the second page. In this way, custom sorting of small data amounts will put much less pressure on MySQL.

This has nothing to do with php. You can create a function with mysql and then use the function to sort:

<code>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, '');</code>

<code>SELECT * FROM test_table order by SPLIT_STRING(test_tablecol, '|||', 3);</code>

It can be implemented, but it is very troublesome and very inefficient. The usual solution to this requirement is to modify the table structure.

From a purely logical point of view, SQL can actually do very complex things, but do you think if a query takes several minutes to run and your family members find out, will you be beaten?

It is recommended to use php to sort after querying, not mysql

substring_index of mysql