mysql - 奇怪的SQL连接产生的结果与拆分结果不同

我正在将表数据从一个CMS导入到另一个CMS。因此，我有一个前缀为“pn_u”的模型，这是旧的模型。新的桌子前面有“avsn_u”。
有游戏变体，画廊图片和画廊类别。桌子看起来像这样：

CREATE TABLE `avsn_gallery_categories2variants` (
  `categoryId` int(255) unsigned NOT NULL AUTO_INCREMENT,
  `gameVariantId` int(255) NULL,
  PRIMARY KEY (`categoryId`,`gameVariantId`)
);

CREATE TABLE `avsn_games_variant` (
        `psId`  int(255) NOT NULL ,
        `variantId`  int(255) NOT NULL AUTO_INCREMENT ,
        `gameId`  int(255) NOT NULL ,
        `variantTitle`  varchar(1000) NOT NULL ,
        `consoleId`  int(255) NOT NULL ,
        `specialGenreID`  int(255) NULL,
        `releaseDay`  int(2) NOT NULL ,
        `releaseMonth`  int(2) NOT NULL ,
        `releaseYear`  int(4) NOT NULL ,
        `isFreeware`  bit NOT NULL ,
        `isReleased`  bit NOT NULL ,
        `installationAdvices`  text NULL ,
        `gameAdvices`  text NULL ,
        `additionalInformation`  text NULL ,
        `cover`  varchar(1000) NOT NULL ,
        `publishingStatus`  int(1) NOT NULL ,
        `creatorId`  int(255) NOT NULL ,
        `lastModified`  date NOT NULL ,
        `creationDate`  date NOT NULL ,
        PRIMARY KEY (`variantId`)
    );

旧桌子是这样的：

CREATE TABLE `pn_gallery_categories` (
  `gallid` int(3) NOT NULL AUTO_INCREMENT,
  `gallname` varchar(80) NOT NULL DEFAULT '',
  `gallimg` varchar(50) NOT NULL DEFAULT '',
  `server` int(11) NOT NULL DEFAULT '1',
  `galloc` longtext NOT NULL,
  `description` text NOT NULL,
  `parent` int(3) NOT NULL DEFAULT '-1',
  `visible` int(1) NOT NULL DEFAULT '0',
  `template` varchar(25) NOT NULL DEFAULT 'Default',
  `thumbwidth` int(2) unsigned NOT NULL DEFAULT '120',
  `numcol` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `total` int(10) unsigned NOT NULL DEFAULT '0',
  `lastadd` date DEFAULT NULL,
  PRIMARY KEY (`gallid`),
  KEY `gallid` (`gallid`)
) ENGINE=MyISAM AUTO_INCREMENT=2166 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

未正确导入表avsn_gallery_categories2 variants。特定类别与变体有多个组合。这应该是不可能的，因为旧模型只允许一个组合。我认为导入中的JOIN有问题：

INSERT INTO
    `avsn_gallery_categories2variants`
    (
        SELECT
            variant.variantId AS `variantId`,
            gallid            AS `categoryId`
        FROM
            pn_gallery_categories
        JOIN
            avsn_games_variant variant ON gallimg = variant.cover
    );

我做了什么来验证：
1确保只有一个类别：

mysql> SELECT * FROM avsn_gallery_categories WHERE categoryId = 1897;
+------------+----------+---------------+---------------+--------+----------------+
| categoryId | parentId | title         | folder        | userId | isUserCategory |
+------------+----------+---------------+---------------+--------+----------------+
|       1897 |      630 | Gray Matter 2 | G/GrayMatter2 |     -1 |                |
+------------+----------+---------------+---------------+--------+----------------+
1 row in set (0.00 sec)

2通过直接请求，确保只有一个关联：

mysql> SELECT * FROM avsn_gallery_categories2variants WHERE categoryId = 1897;
+------------+---------------+
| categoryId | gameVariantId |
+------------+---------------+
| 1897 | 365 |
| 1897 | 542 |
+------------+---------------+
2 rows in set (0.01 sec)

哎呀！两排。好的，现在检查两个变量的连接字段：
该类别的封面：

mysql> SELECT gallimg FROM pn_gallery_categories WHERE gallid = 1897;
+-----------------+
| gallimg         |
+-----------------+
| graymatter2.png |
+-----------------+
1 row in set (0.00 sec)

编号365：

mysql> SELECT cover FROM avsn_games_variant WHERE variantId = 365;
+-----------+
| cover     |
+-----------+
| radau.jpg |
+-----------+
1 row in set (0.00 sec)

编号542：

mysql> SELECT cover FROM avsn_games_variant WHERE variantId = 542;
+-----------+
| cover     |
+-----------+
| tkkg5.jpg |
+-----------+
1 row in set (0.00 sec)

呃。是 啊。它们都不符合这一类别怎么了？我有点绝望地寻找解决办法。Mabye我写了太多的SQL，并且已经看不到问题了，因为有一个大约5，5k行的导入脚本。
我很感激你的回答！

最佳答案

我想应该是：

INSERT INTO
    `avsn_gallery_categories2variants`
    (
        SELECT
            gallid            AS `categoryId`,
            variant.variantId AS `variantId`
        FROM
            pn_gallery_categories
        JOIN
            avsn_games_variant variant ON gallimg = variant.cover
    );

或者更好：

INSERT INTO
    `avsn_gallery_categories2variants`
    (`categoryId`, `variantId`)
    (
        SELECT
            gallid,
            variant.variantId
        FROM
            pn_gallery_categories
        JOIN
            avsn_games_variant variant ON gallimg = variant.cover
    );

我从the official documentation中得知，如果不显式声明目标列，那么内部select中列的顺序应该与目标表中列的顺序匹配。我认为在select部分中给列加别名没有任何效果。