mysqldump where子句使用

mysqldump 的 where 条件子句适用于这种情况：导出某张表的部分数据。

--where=name 参数详情：

-w, --where=name    Dump only selected records. Quotes are mandatory.

使用 where 条件的时候，引号是必须的。where 条件的使用和 sql 语句中 where 是相同的。

语法如下：
1.mysqldump -u username -p password -B dbname --tables tbl --where="clause" > /path/file.sql
or
2.mysqldump -u username -p password --tables dbname tbl --where="clause" > /path/file.sql
or
3.mysqldump -u username -p password -B dbname --tables tbl1 tbl2 --where="clause" > /path/file.sql

注：第3条如果要 dump 多张表，where 条件中涉及的列必须在两张表中都有。

例子：
1.mysqldump -uroot -p -B db1 --tables keith --where="price * count = 10" > /root/keith.sql

表数据

mysql> select * from `keith`;
+----+-------+-------+
| id | count | price |
+----+-------+-------+
|  1 |     1 |    10 |
|  2 |     2 |     5 |
|  3 |     3 |     4 |
|  4 |     2 |     6 |
+----+-------+-------+

备份文件

-- MySQL dump 10.13  Distrib 8.0.27, for Linux (x86_64)
--
-- Host: localhost    Database: db1
-- ------------------------------------------------------
-- Server version       8.0.27
--
-- Table structure for table `keith`
--

DROP TABLE IF EXISTS `keith`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `keith` (
  `id` int NOT NULL AUTO_INCREMENT,
  `count` int NOT NULL,
  `price` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `keith`
--
-- WHERE:  price * count = 10

LOCK TABLES `keith` WRITE;
/*!40000 ALTER TABLE `keith` DISABLE KEYS */;
INSERT INTO `keith` VALUES (1,1,10),(2,2,5);
/*!40000 ALTER TABLE `keith` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2021-12-10 11:30:44

2.mysqldump -uroot -p --tables db1 keith --where="price * count = 10 and count = 2" > /root/keith.sql

备份文件部分内容

--
-- Dumping data for table `keith`
--
-- WHERE:  price * count = 10 and count = 2

LOCK TABLES `keith` WRITE;
/*!40000 ALTER TABLE `keith` DISABLE KEYS */;
INSERT INTO `keith` VALUES (2,2,5);
/*!40000 ALTER TABLE `keith` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

3.mysqldump -uroot -p -B db1 --tables keith wayne --where="price=5" > /root/keith.sql