mysqldump工具默认情况下是按照表的字典顺序(即表名的字母顺序)来导出表的,而不是根据表之间的主外键依赖关系。这意味着如果数据库中的表存在外键约束,并且数据导出顺序对还原时保持数据完整性很重要,直接使用不带特殊选项的mysqldump可能不会按照依赖关系正确地导出数据。
如果需要严格按依赖排序,可能需要自定义脚本或使用其他工具来先确定依赖关系,再按序导出。
mysqldump 默认按字母顺序导出表的示例
连接到本机MySQL服务器,用户名为admin,之后手动输入密码:
mysql -u admin -h localhost -p
create database db1;
use db1;
## products (商品表)
CREATE TABLE `products` (
`product_id` INT(11) NOT NULL AUTO_INCREMENT,
`product_name` VARCHAR(255) NOT NULL,
`price` DECIMAL(10, 2) DEFAULT NULL,
PRIMARY KEY (`product_id`)
);
## orders (订单表),含有对商品表的外键引用
CREATE TABLE `orders` (
`order_id` INT(11) NOT NULL AUTO_INCREMENT,
`customer_id` INT(11) NOT NULL,
`product_id` INT(11) DEFAULT NULL,
`quantity` INT(11) DEFAULT NULL,
PRIMARY KEY (`order_id`),
KEY `fk_products_idx` (`product_id`),
CONSTRAINT `fk_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON DELETE SET NULL ON UPDATE CASCADE
);
mysqldump -u admin -h localhost -p db1 >db1.sql
生成的db1.sql的内容如下:
– MySQL dump 10.13 Distrib 8.0.35, for Win64 (x86_64)
–
– Host: localhost Database: db1
– Server version 8.0.35
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!50503 SET NAMES utf8mb4 /; /!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE
/; /!40103 SET TIME_ZONE=‘+00:00’ /; /!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /; /!40014 SET
@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /; /!40111 SET
@OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;–
– Table structure for tableorders
DROP TABLE IF EXISTS
orders
; /*!40101 SET @saved_cs_client =
@@character_set_client /; /!50503 SET character_set_client = utf8mb4
/; CREATE TABLEorders
(order_id
int NOT NULL AUTO_INCREMENT,customer_id
int NOT NULL,product_id
int DEFAULT NULL,
quantity
int DEFAULT NULL, PRIMARY KEY (order_id
), KEY
fk_products_idx
(product_id
), CONSTRAINTfk_products
FOREIGN
KEY (product_id
) REFERENCESproducts
(product_id
) ON DELETE SET
NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci; /!40101 SET character_set_client =
@saved_cs_client */;–
– Dumping data for tableorders
LOCK TABLES
orders
WRITE; /*!40000 ALTER TABLEorders
DISABLE KEYS
/; /!40000 ALTER TABLEorders
ENABLE KEYS */; UNLOCK TABLES;–
– Table structure for tableproducts
DROP TABLE IF EXISTS
products
; /*!40101 SET @saved_cs_client =
@@character_set_client /; /!50503 SET character_set_client = utf8mb4
/; CREATE TABLEproducts
(product_id
int NOT NULL AUTO_INCREMENT,product_name
varchar(255) NOT NULL,price
decimal(10,2) DEFAULT NULL, PRIMARY KEY (product_id
) )
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/!40101 SET character_set_client = @saved_cs_client */;–
– Dumping data for tableproducts
LOCK TABLES
products
WRITE; /*!40000 ALTER TABLEproducts
DISABLE
KEYS /; /!40000 ALTER TABLEproducts
ENABLE KEYS /; UNLOCK
TABLES; /!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE /; /!40014 SET
FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /; /!40014 SET
UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /; /!40101 SET
CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /; /!40101 SET
CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /; /!40101 SET
COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /; /!40111 SET
SQL_NOTES=@OLD_SQL_NOTES */;– Dump completed on 2024-05-08 9:41:53
这个例子说明了,如果直接使用mysqldump不加特别排序选项的话,表orders的确会先于products被导出,因为o在字母表中位于p之前。但是,正如之前讨论的,这样的导出顺序可能会导致问题,尤其是在导入数据时,因为orders表中引用了products表的外键。
mysqldump手动按顺序导出导入表的示例
对于products
和orders
这两个表,考虑到orders
表中有一个外键引用了products
表的product_id
,正确的导出和导入步骤如下,以确保数据的一致性和完整性:
导出步骤
-
单独导出
products
表首先,单独导出
products
表,确保所有产品信息先被保存下来,不会受到后续操作的影响。mysqldump -u [username] -h [hostname] -p[password] [database_name] products > products.sql
-
单独导出
orders
表接着,导出
orders
表,该表包含对products
表的引用。mysqldump -u [username] -p[password] [database_name] orders > orders.sql
导入步骤
在新的数据库环境中,按照依赖关系顺序进行导入:
-
创建数据库(如果需要)
如果目标数据库不存在,首先创建数据库。
mysql -u [username] -h [hostname] -p[password] -e "CREATE DATABASE IF NOT EXISTS [new_database_name];"
-
导入
products
表首先导入
products
表的结构和数据,因为它是被orders
表所依赖的。mysql -u [username] -h [hostname] -p[password] [new_database_name] < products.sql
-
导入
orders
表确保
products
表已经成功导入后,再导入orders
表,这样外键约束可以正常应用。mysql -u [username] -h [hostname] -p[password] [new_database_name] < orders.sql
如果您是想了解如何使用SQL查询来展示数据库表之间的外键依赖关系,以便于在使用mysqldump之前手动安排导出顺序,这里有一个基本的SQL示例,它适用于MySQL数据库来查询哪些表引用了哪些其他表作为外键。这可以帮助您手动决定导出的顺序。
SELECT
TABLE_NAME AS ' referencing_table',
REFERENCED_TABLE_NAME AS 'referenced_table'
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = 'db1' AND REFERENCED_TABLE_NAME IS NOT NULL;
±------------------±-----------------+ | referencing_table | referenced_table |
±------------------±-----------------+ | orders | products |
±------------------±-----------------+
这个查询会列出所有具有外键约束的表,以及它们各自引用的表。基于这个结果,您可以决定先导出哪些表(通常是那些没有被其他表引用的表,即没有作为REFERENCED_TABLE_NAME出现的表),然后再导出那些有外键指向其他表的表。