主页 / dba / 问题 / 345010
Accepted
Marlin Pierce
Asked: 2025-01-31 00:30:01 +0800 CST

mysqldump 命令在输出中省略了排序规则

  • 772

当我运行 mysqldump 时，输出不包含某些表的表默认排序规则。如何包含所有表的排序规则？

MySQL版本是5.7。

mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.38, for FreeBSD13.1 (amd64)

命令行如下（但带有多个 --ignore-table 开关）

mysqldump --defaults-file=.fmd-acdb-dev.cnf my-db-name --set-gtid-purged=OFF

cnf文件有以下内容：

[client]
host=localhost
user=root
password=[redacted]
max_allowed_packet=1073741824

以下是输出的选择。

/*!40000 ALTER TABLE `cluster_categorizations` DISABLE KEYS */;
-- MySQL dump 10.13  Distrib 5.7.38, for FreeBSD13.1 (amd64)
--
-- Host: localhost    Database: analyst_console_condense
-- ------------------------------------------------------
-- Server version       5.7.38-log

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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 */;
...
DROP TABLE IF EXISTS `false_positive_selections`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `false_positive_selections` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `display` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
...
DROP TABLE IF EXISTS `companies`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `companies` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_companies_on_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=32957 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

为了进行比较，在被转储的源数据库中：

show create table false_positive_selections;
CREATE TABLE `false_positive_selections` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `display` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'my-schema';
utf8mb4_general_ci

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'false_positive_selections';
| my-schema | false_positive_selections | utf8mb4_general_ci |

恢复后在目标数据库中：

show create table false_positive_selections;
CREATE TABLE `false_positive_selections` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `display` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'my-schema';
utf8mb4_0900_ai_ci

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'false_positive_selections';
| my-schema | false_positive_selections | utf8mb4_0900_ai_ci |

为了应对数据库默认排序规则的潜在差异，我们正在尝试标准化每个表的默认排序规则设置。

mysql

1 个回答

  1. Best Answer
    • /*!40101 SET character_set_client = utf8 */;

    is a special comment that turns into 

           SET character_set_client = utf8 ;

    如果运行的版本 >= 4.1.1

    因此，我认为输出确实设置了字符集。

    • 省略的规则是COLLATION，您将获得“默认”排序规则，utf8_general_ci即CHARACTER SET utf8和。utf8mb4_general_ciCHARACTER SET utf8mb4

    • 如果您有一个采用其他排序规则的表，请提供SHOW CREATE TABLE（转储之前）。

    • 0

相关问题