DataTables无法按Levenshtein相似度列排序，触发SQL异常

报错根源 #

你在模型的$column_order中添加了result_similarity，但这个字段并非数据库表article的真实列。当DataTables触发服务器端排序时，模型会生成包含ORDER BY result_similarity的SQL语句，数据库无法识别该虚拟字段，因此抛出Unknown column 'similarity' in 'order clause'错误。

以下是两种可行的解决方案：

方案1：客户端处理相似度排序（适合小数据量） #

直接让DataTables在前端对返回的相似度列排序，无需修改数据库查询逻辑。

1. 修改模型代码 #

移除$column_order中的result_similarity，禁止服务器端尝试用虚拟字段排序：

var $table = 'article';
var $column_order = array(null,'title'); // 移除result_similarity
var $column_search = array('title'); 
var $order = array('title' => 'asc'); 

2. 前端DataTables配置 #

初始化表格时，将相似度列指定为数值类型并允许排序：

$('#your-table-id').DataTable({
    ajax: '/your/ajax/list/url',
    columns: [
        { data: 0, orderable: false }, // 序号列不可排序
        { data: 1 }, // 标题列
        { data: 2, type: 'num' } // 相似度列，指定数值类型确保排序正确
    ]
});

3. 控制器代码保留不变 #

继续在PHP中计算相似度后返回给前端，由前端负责排序逻辑。

方案2：服务器端SQL计算相似度（适合大数据量） #

通过MySQL自定义函数在SQL层面计算相似度，支持服务器端排序和分页。

1. 创建MySQL Levenshtein函数 #

若你的MySQL未内置该函数，先执行以下SQL创建自定义函数：

DELIMITER //
CREATE FUNCTION LEVENSHTEIN(s1 VARCHAR(255), s2 VARCHAR(255)) 
RETURNS INT 
DETERMINISTIC
BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp INT;
    DECLARE s1_char CHAR;
    DECLARE cv0, cv1 VARBINARY(256);
    
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2);
    IF s1_len = 0 THEN RETURN s2_len; END IF;
    IF s2_len = 0 THEN RETURN s1_len; END IF;
    
    SET cv0 = 0x00;
    FOR i FROM 1 TO s2_len DO
        SET cv0 = CONCAT(cv0, UNHEX(HEX(i)));
    END FOR;
    
    FOR i FROM 1 TO s1_len DO
        SET s1_char = SUBSTRING(s1, i, 1);
        SET cv1 = UNHEX(HEX(i));
        SET j = 1;
        WHILE j <= s2_len DO
            SET c = IF(s1_char = SUBSTRING(s2, j, 1), 0, 1);
            SET c_temp = CONV(HEX(SUBSTRING(cv0, j, 1)), 16, 10) + c;
            SET cv1 = CONCAT(cv1, UNHEX(HEX(LEAST(
                CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + 1,
                c_temp,
                CONV(HEX(SUBSTRING(cv0, j+1, 1)), 16, 10) + 1
            ))));
            SET j = j + 1;
        END WHILE;
        SET cv0 = cv1;
    END FOR;
    
    RETURN CONV(HEX(SUBSTRING(cv0, s2_len+1, 1)), 16, 10);
END //
DELIMITER ;

2. 修改模型查询逻辑 #

在_get_datatables_query方法中加入相似度计算：

private function _get_datatables_query()
{
    $searchinput = $_POST['search']['value'];
    
    // 加入相似度计算作为查询字段
    $this->db->select('article.*, (1 - LEVENSHTEIN(title, ?)/GREATEST(LENGTH(title), LENGTH(?))) * 100 AS result_similarity', false);
    $this->db->from($this->table);
    
    // 绑定搜索关键词到函数参数
    $this->db->bind_param('ss', $searchinput, $searchinput);
    
    // 原有搜索逻辑保留
    if(!empty($searchinput)){
        $this->db->like('title', $searchinput);
    }
    
    $i = 0;
    foreach ($this->column_search as $item)
    {
        if($_POST['search']['value'])
        {
            if($i===0) $this->db->group_start();
            $this->db->like($item, $_POST['search']['value']);
            if(count($this->column_search) - 1 == $i) $this->db->group_end();
        }
        $i++;
    }
    
    // 排序逻辑保留，现在可识别result_similarity别名
    if(isset($_POST['order']))
    {
        $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    } 
    else if(isset($this->order))
    {
        $order = $this->order;
        $this->db->order_by(key($order), $order[key($order)]);
    }
}

3. 模型配置保留原设置 #

var $column_order = array(null,'title','result_similarity');
var $column_search = array('title'); 
var $order = array('title' => 'asc'); 

4. 修改控制器代码 #

直接从模型结果中获取预计算的相似度值：

public function ajax_list()
{           
    $list = $this->article->get_datatables();
    $data = array();
    $no = $_POST['start'];
    foreach ($list as $article) {
        $no++;
        $row = array();
        $row[] = $no;
        $row[] = $article->title;
        $row[] = $article->result_similarity; // 直接取SQL计算好的值
        $data[] = $row;
    }
    $output = array(
                    "draw" => $_POST['draw'],
                    "recordsTotal" => $this->article->count_all(),
                    "recordsFiltered" => $this->article->count_filtered(),
                    "data" => $data,
            );
    echo json_encode($output);
}

内容的提问来源于stack exchange，提问作者Adi