溪客(编程代码)

使用pgloader https://github.com/dimitri/pgloader https://pgloader.readthedocs.io/en/latest/ref/mysql.html ``` LOAD DATABASE FROM mysql://pwd:root@localhost/sakila INTO postgresql://pwd:username@localhost:54393/sakila WITH include drop, create tables, create indexes, reset sequences,quote identifiers, workers = 8, concurrency = 1, multiple readers per thread, rows per range = 50000 SET PostgreSQL PARAMETERS maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'sakila, public, "$user"' SET MySQL PARAMETERS net_read_timeout = '120', net_write_timeout = '120' CAST type bigint when (= precision 20) to bigserial drop typemod, type date drop not null drop default using zero-dates-to-null, -- type tinyint to boolean using tinyint-to-boolean, type year to integer MATERIALIZE VIEWS film_list, staff_list -- INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor' -- EXCLUDING TABLE NAMES MATCHING ~ -- DECODING TABLE NAMES MATCHING ~/messed/, ~/encoding/ AS utf8 -- ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films' -- ALTER TABLE NAMES MATCHING ~/_list$/ SET SCHEMA 'mv' ALTER SCHEMA 'cscodercn-txd' RENAME TO 'public' ALTER TABLE NAMES MATCHING ~/_list$/, 'sales_by_store', ~/sales_by/ SET SCHEMA 'mv' ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films' ALTER TABLE NAMES MATCHING ~/./ SET (fillfactor='40') ALTER SCHEMA 'sakila' RENAME TO 'pagila' BEFORE LOAD DO $$ create schema if not exists pagila; $$, $$ create schema if not exists mv; $$, $$ alter database sakila set search_path to pagila, mv, public; $$; ``` 以下是上面的逐条中文解释，帮助你理解每一部分的含义和作用： --- ## 🧩 整体结构 这是一个 **pgloader 的配置脚本**，用于从 **MySQL 数据库**（`mysql://root@localhost/sakila`）导入数据到 **PostgreSQL 数据库**（`postgresql://localhost:54393/sakila`），并进行一系列控制，比如表结构创建、索引、数据类型转换、并发控制、视图物化、表重命名、Schema 管理等。 --- ## 📌 完整配置及逐行中文解释 --- ### 1. 基本导入设置 ```lisp LOAD DATABASE FROM mysql://pwd:root@localhost/sakila INTO postgresql://pwd:username@localhost:54393/sakila ``` - **作用**：定义数据迁移的源和目标。 - **解释**： - **源数据库**：MySQL，地址是 `localhost`，用户是 `root`，数据库名是 `sakila`。 - **目标数据库**：PostgreSQL，地址是 `localhost`，端口是 `54393`（非默认 5432），数据库名是 `sakila`。 --- ### 2. 导入行为控制（WITH 子句） ```lisp WITH include drop, create tables, create indexes, reset sequences, workers = 8, concurrency = 1, multiple readers per thread, rows per range = 50000 ``` - **作用**：控制 pgloader 在导入时的具体行为。 - **逐项解释**： | 选项 | 说明 | |------|------| | `include drop` | **在导入前，先删除 PostgreSQL 中已存在的表**（如果目标表已存在，则先删除再重建）。**慎用！会丢失已有数据。** | | `create tables` | 在目标 PostgreSQL 中**自动创建表结构**（根据 MySQL 的表结构生成对应的 PostgreSQL 表）。 | | `create indexes` | **在导入数据后，自动为表创建索引**（根据 MySQL 的索引定义在 PostgreSQL 中重建索引）。 | | `reset sequences` | **重置 PostgreSQL 中的自增序列（如 serial / bigserial 字段对应的 sequence）**，确保下次插入时 ID 从正确的值开始（通常是 MySQL 表中当前的最大 ID + 1）。 | | `workers = 8` | 使用 **8 个并行工作线程** 来加速数据导入（多线程导入，提升性能）。 | | `concurrency = 1` | 控制并发连接数，这里设为 1（与 workers 配合使用，通常保持默认即可）。 | | `multiple readers per thread` | 每个 worker 线程可以同时读取多个数据块，提高吞吐量。 | | `rows per range = 50000` | 每次从 MySQL 读取 **5 万行数据作为一个批次** 进行迁移，优化内存和速度。 | | `quote identifiers` | 加引号,保留标识符（表名、列名）的大小写 | --- ### 3. 设置 PostgreSQL 参数 ```lisp SET PostgreSQL PARAMETERS maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'sakila, public, "$user"' ``` - **作用**：在导入过程中，**临时设置 PostgreSQL 的运行参数，优化导入性能或行为**。 - **逐项解释**： | 参数 | 说明 | |------|------| | `maintenance_work_mem to '128MB'` | 设置 PostgreSQL 执行维护任务（如创建索引、VACUUM 等）时使用的内存为 128MB，有助于加速索引创建等操作。 | | `work_mem to '12MB'` | 设置每个查询操作（如排序、哈希等）可用的内存为 12MB，影响查询性能。 | | `search_path to 'sakila, public, "$user"'` | 设置 PostgreSQL 的搜索路径，即查找表、视图等对象的顺序：先找 `sakila` schema，然后 `public`，最后是当前用户对应的 schema。 | > ⚠️ 注意：这些参数 **只在 pgloader 执行期间生效**，不会永久修改 PostgreSQL 服务器配置。 --- ### 4. 设置 MySQL 参数 ```lisp SET MySQL PARAMETERS net_read_timeout = '120', net_write_timeout = '120' ``` - **作用**：设置 MySQL 连接参数，防止因大数据量迁移导致连接超时。 - **逐项解释**： | 参数 | 说明 | |------|------| | `net_read_timeout = '120'` | MySQL 读取数据的超时时间设为 120 秒（防止读取大块数据超时断开）。 | | `net_write_timeout = '120'` | MySQL 写入数据的超时时间设为 120 秒。 | > 适用于大数据量迁移时，防止网络或慢查询导致连接中断。 --- ### 5. 数据类型转换规则（CAST） ```lisp CAST type bigint when (= precision 20) to bigserial drop typemod, type date drop not null drop default using zero-dates-to-null, -- type tinyint to boolean using tinyint-to-boolean, type year to integer ``` - **作用**：定义如何将 MySQL 的数据类型映射/转换为 PostgreSQL 的数据类型。 - **逐项解释**： | 转换规则 | 说明 | |----------|------| | `type bigint when (= precision 20) to bigserial drop typemod` | 如果 MySQL 中的字段类型是 `bigint(20)`，则导入到 PostgreSQL 时转换为 `bigserial`（自增大整数），并丢弃精度等附加信息。 | | `type date drop not null drop default using zero-dates-to-null` | MySQL 的 `date` 类型导入为 PostgreSQL 的 `date`，同时：如果原字段有 `NOT NULL` 或 `DEFAULT` 约束，**这里会去掉**，并且使用 `zero-dates-to-null`（将 `'0000-00-00'` 这种非法日期转为 `NULL`）。 | | `-- type tinyint to boolean using tinyint-to-boolean` | **这行被注释掉了**，如果启用，表示将 MySQL 的 `tinyint(1)` 转为 PostgreSQL 的 `boolean`（常用于表示布尔值，如 0=false, 1=true）。 | | `type year to integer` | 将 MySQL 的 `year` 类型（如 2024）导入为 PostgreSQL 的普通 `integer` 类型。 | --- ### 6. 物化视图（MATERIALIZED VIEWS） ```lisp MATERIALIZE VIEWS film_list, staff_list ``` - **作用**：将 MySQL 中的 **视图（VIEW）** `film_list` 和 `staff_list` **作为物化视图导入到 PostgreSQL**（即导入为实际的表，保存查询结果）。 - **说明**：pgloader 支持将 MySQL 的视图以物化方式导入，但注意它实际上是**把视图查询结果拉取出来存成表**，不是真正的 PostgreSQL 物化视图（MATERIALIZED VIEW）。 --- ### 7. 表重命名与 Schema 管理 #### 7.1 修改表的Schema ``` ALTER SCHEMA 'cscodercn-txd' RENAME TO 'public' ``` #### 7.2 指定部分表迁移到特定 Schema ```lisp ALTER TABLE NAMES MATCHING ~/_list$/, 'sales_by_store', ~/sales_by/ SET SCHEMA 'mv' ``` - **作用**：将**表名匹配某些正则表达式**的表迁移到 PostgreSQL 的 `mv` schema 中（而不是默认的 public）。 - **匹配规则**： - 表名以 `_list$` 结尾的（如 `film_list`） - 表名是 `'sales_by_store'` - 表名匹配正则 `~/sales_by/`（可能是包含 sales_by 的表名） - **结果**：这些表会被创建在 `mv` 这个 schema 下，而不是默认的 `public`。 #### 7.2 表重命名 ```lisp ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films' ``` - **作用**：将 MySQL 中的表名 `film`，在导入到 PostgreSQL 时**改名为 `films`**。 - **说明**：只对表名严格匹配 `'film'` 的表生效。 #### 7.3 设置表的填充因子（Fillfactor） ```lisp ALTER TABLE NAMES MATCHING ~/./ SET (fillfactor='40') ``` - **作用**：对**所有表**（`~/./` 表示匹配任意表名），设置 PostgreSQL 表的 `fillfactor` 为 40。 - **说明**： - `fillfactor` 是 PostgreSQL 特有的存储参数，表示表页的填充率（40%），留出空间便于后续更新，适合频繁更新的表。 - 这里是对**所有表都设置 fillfactor=40**，一般用于优化更新密集型表，但会影响存储密度。 --- ### 8. Schema 重命名 ```lisp ALTER SCHEMA 'sakila' RENAME TO 'pagila' ``` - **作用**：将导入后默认生成的 schema（通常是 `sakila`）**重命名为 `pagila`**。 - **说明**：如果你使用了 `INCLUDE SCHEMA` 或默认导入到一个 schema，这个配置会在导入后将其改名。 > ⚠️ 注意：这个重命名的是 **PostgreSQL 中的 schema 名称**，不是 MySQL 的。 --- ### 9. 导入前的自定义 SQL（BEFORE LOAD DO） ```lisp BEFORE LOAD DO $$ create schema if not exists pagila; $$, $$ create schema if not exists mv; $$, $$ alter database sakila set search_path to pagila, mv, public; $$ ``` - **作用**：在正式导入数据 **之前**，先执行几条 PostgreSQL 的 SQL 语句。 - **逐条解释**： | SQL 语句 | 说明 | |---------|------| | `create schema if not exists pagila;` | 如果 PostgreSQL 中不存在 `pagila` 这个 schema，就创建它。 | | `create schema if not exists mv;` | 如果不存在 `mv` schema，也提前创建（用于存放某些表，如视图类表）。 | | `alter database sakila set search_path to pagila, mv, public;` | 将 PostgreSQL 数据库 `sakila` 的默认 **search_path**（查找表、视图等的顺序）设为：先找 `pagila`，然后 `mv`，最后是 `public`。 | > 这样在导入后，查询表时优先从 `pagila` schema 查找，更加方便，避免每次都要写 `pagila.表名`。 --- ## ✅ 总结（中文简明版） | 配置部分 | 功能 | 作用 | |---------|------|------| | **FROM / INTO** | 源和目标数据库 | 从 MySQL 的 sakila 库导入到 PostgreSQL 的 sakila 库（端口 54393） | | **WITH 子句** | 导入行为控制 | 包括：删除旧表、建表、建索引、重置序列、多线程并发导入等 | | **PostgreSQL PARAMETERS** | 优化参数 | 设置 work_mem、maintenance_work_mem、search_path 提升导入性能与查找效率 | | **MySQL PARAMETERS** | 防超时设置 | 避免因数据量大导致连接中断 | | **CAST** | 数据类型转换 | 比如将 MySQL 的 bigint(20) 转为 PostgreSQL 的 bigserial，处理 date / year 类型等 | | **MATERIALIZE VIEWS** | 视图物化 | 将 MySQL 的视图导入为实际表（保存查询结果） | | **ALTER TABLE ... SET SCHEMA** | 表迁移到指定 schema | 比如将某些表放入 `mv` schema | | **ALTER TABLE ... RENAME TO** | 表重命名 | 比如将 `film` 表改名为 `films` | | **ALTER TABLE ... SET** | 表参数设置 | 比如设置所有表的 fillfactor=40 | | **ALTER SCHEMA ... RENAME TO** | schema 重命名 | 将默认 schema 从 `sakila` 改为 `pagila` | | **BEFORE LOAD DO** | 导入前执行 SQL | 提前创建 schema、设置 search_path，优化后续使用 | --- 如你希望我帮你调整某一部分（比如不删除已有表、不重命名 schema、只导入部分表等），可以继续提问！