php - 跨不同的 MySQL 列进行不区分大小写的搜索

Question

在我的网站上，我集成了搜索功能。我尝试了全文搜索，但它并不像我希望的那样不区分大小写。艺术家和歌曲在 MySQL 中位于不同的列中。

代码是:

public function search($query, $page_num = 1, $cid=null) {
    if (empty($page_num)) $page_num = 1;
    $items = $this->db->get_results('SELECT added, store.id, categories.id AS category_id, categories.name AS category_name, artist, song, file, size, youtube_url, downloads, hits
        FROM store LEFT JOIN categories ON (store.cid = categories.id)

        WHERE MATCH(artist, song, added) AGAINST ("+' . $this->db->escape($query) . '" IN BOOLEAN MODE)

        '. ($cid!==null ? ' AND store.cid = "'.intval($cid).'"' : '') .'
        ORDER BY added DESC
        LIMIT ' . ($page_num - 1) * SONGS_ON_PAGE . ', ' . SONGS_ON_PAGE, ARRAY_A);

Answer 1

您可以从这样的查询中返回结果:

SELECT * FROM store
WHERE CONCAT(artist, song) LIKE '%<search>%'