mysql - 查询 - 在简单的 mysql 内连接中定义(表，表)

Question

我正在重建一个搜索查询，因为它在“我看到的”中变得多余，我想知道什么

(albums_artists, artists) ( ) does in join? is it for boosting performance?

使用简单内连接的查询，使用旧的 (SQL-89) 隐式连接语法:

    SELECT 
    ma_users.name, 
    ma_users.username, 
    albums.id AS album_id, 
    albums.upc, 
    albums.name AS album_name,
    albums.status, 
    albuminfos.label, 
    DATE_FORMAT(albums.created, '%Y-%m-%d') AS created_date,
    CONCAT(artists.name) AS artist_name,
    COUNT(tracks.id) AS total_tracks,
    albumstatus.description AS album_status
    FROM albums, albuminfos, ma_users , (albums_artists, artists) , tracks ,(albumstatus, albumtypes)
    WHERE 
    albums.id = albuminfos.id 
    AND ma_users.id = albums.account_id
    AND albums.id = albums_artists.artist_id 
    AND albums_artists.artist_id = artists.id
    AND tracks.album_id = albums.id
    AND albums.status = albumstatus.id 
    AND albumtypes.id = albums.albumtype_id
    AND albuminfos.label LIKE '%$keywords%'
    GROUP BY albums.id
    ORDER BY albuminfos.label

也接受对我需要预期的改进和错误的意见。

Answer 1

据我所知，这些括号是多余的并且无助于查询执行 - 可能认为通过将它们括起来，这些表将被区别对待，但我不知道这是真的(甚至不需要在这里)。

顺便问一下，是 vma_users 还是 ma_users？ (大多数引用都是没有 v 的 ma_users)

就我个人而言，我不赞成 MySQL 允许 GROUP BY 是“惰性”的方式，而更喜欢列出所有参与分组的字段。而且，我也不会坚持使用那些旧的连接样式。没有看到任何语法错误，对于增强功能，我认为没有足够的信息来发表评论。

SELECT
       ma_users.NAME
     , ma_users.username
     , albums.id   AS album_id
     , albums.upc
     , albums.NAME AS album_name
     , albums.STATUS
     , albuminfos.label
     , DATE_FORMAT(albums.created, '%Y-%m-%d') AS created_date
     , CONCAT (artists.NAME) AS artist_name
     , COUNT(tracks.id) AS total_tracks
     , albumstatus.description AS album_status
FROM albums
INNER JOIN albuminfos     ON albums.id = albuminfos.id
/* Vma_users ?? */
INNER JOIN ma_users       ON albums.account_id = ma_users.id
INNER JOIN albums_artists ON albums.id = albums_artists.artist_id
INNER JOIN artists        ON albums_artists.artist_id = artists.id
INNER JOIN tracks         ON albums.id = tracks.album_id
INNER JOIN albumstatus    ON albums.STATUS = albumstatus.id
INNER JOIN albumtypes     ON albums.albumtype_id =albumtypes.id
WHERE albuminfos.label LIKE '%$keywords%'
GROUP BY
       ma_users.NAME
     , ma_users.username
     , albums.id
     , albums.upc
     , albums.NAME
     , albums.STATUS
     , albuminfos.label
     , albums.created /* assuming it's only date with times of 00:00:00 */
     , albumstatus.description
ORDER BY albuminfos.label

好吧:我忽略了不一致；我同意那些发现它的人INNER JOIN albums_artists ON albums.id = albums_artists.artist_id看起来不对