mysqldump生成了一个损坏的sql文件(显然是在具有存储生成列的表上)(MySQL v8.0.21)

Question

我使用的是MySQL v8.0.21和mysqldump v8.0.23。

我有一个具有以下模式的表：

mysql> desc resource_downloads;
+-------------+--------------+------+-----+---------+------------------+
| Field       | Type         | Null | Key | Default | Extra            |
+-------------+--------------+------+-----+---------+------------------+
| compositeId | varchar(250) | NO   | PRI | NULL    | STORED GENERATED |
| resourceId  | int unsigned | NO   | MUL | NULL    |                  |
| userId      | int unsigned | NO   | MUL | NULL    |                  |
| fileId      | int unsigned | YES  | MUL | NULL    |                  |
+-------------+--------------+------+-----+---------+------------------+

以及以下值：

mysql> select * from resource_downloads;
+-------------+------------+--------+--------+
| compositeId | resourceId | userId | fileId |
+-------------+------------+--------+--------+
| 24-150-NULL |         24 |    150 |   NULL |
| 37-150-NULL |         37 |    150 |   NULL |
| 56-150-48   |         56 |    150 |     48 |
| 56-150-NULL |         56 |    150 |   NULL |
+-------------+------------+--------+--------+
4 rows in set (0.00 sec)

以下是创建表的SQL语句：

CREATE TABLE `resource_downloads` (`compositeId` varchar(250) AS (CONCAT_WS('-', resourceId, userId, IFNULL(fileId, 'NULL'))) STORED NOT NULL, `resourceId` int UNSIGNED NOT NULL, `userId` int UNSIGNED NOT NULL, `fileId` int UNSIGNED NULL, PRIMARY KEY (`compositeId`)) ENGINE=InnoDB

执行以下mysqldump命令时：

mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME > dump.sql

它确实为resource_downloads表生成以下内容：

--
-- Dumping data for table `resource_downloads`
--
LOCK TABLES `resource_downloads` WRITE;
/*!40000 ALTER TABLE `resource_downloads` DISABLE KEYS */;
INSERT INTO `resource_downloads` (`resourceId`, `userId`, `fileId`) VALUES ,24,150,NULL),,24,150,NULL),37,150,NULL),,24,150,NULL),37,150,NULL),56,150,48),,24,150,NULL),37,150,NULL),56,150,48),56,150,NULL);
/*!40000 ALTER TABLE `resource_downloads` ENABLE KEYS */;
UNLOCK TABLES;

如您所见，它已损坏，不是有效的SQL语句。

我没有发现任何与这种特殊情况相对应的内容，我想这是由于生成的存储列，因为它是几天前我在数据库中引入它之后才出现的。

对于这种情况，有什么解决方法可以让我生成数据库的可用转储吗？我可以考虑把这张桌子从垃圾堆里排除出去，但这不方便。。。

Answer 1

如果将生成的列移动到末尾，如下所示：

CREATE TABLE `resource_downloads` (
`resourceId` int UNSIGNED NOT NULL, 
`userId` int UNSIGNED NOT NULL, 
`fileId` int UNSIGNED NULL, 
`compositeId` varchar(250) AS (CONCAT_WS('-', resourceId, userId, IFNULL(fileId, 'NULL'))) STORED NOT NULL, 
PRIMARY KEY (`compositeId`)
) ENGINE=InnoDB;

而不是这样：

CREATE TABLE `resource_downloads` (
`compositeId` varchar(250) AS (CONCAT_WS('-', resourceId, userId, IFNULL(fileId, 'NULL'))) STORED NOT NULL, 
`resourceId` int UNSIGNED NOT NULL, 
`userId` int UNSIGNED NOT NULL, 
`fileId` int UNSIGNED NULL, 
PRIMARY KEY (`compositeId`)
) ENGINE=InnoDB;

mysqldump似乎还可以。(用mysqldump 8.0.25测试(