求写一段生成数据库测试数据的代码

[shendaowu]

问题我问过 ChatGPT 了，3.5 和 4 都问过，一直没试出来满意的代码。

假设有如下三个 MariaDB 表：

CREATE TABLE tag(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
description VARCHAR(1000));

CREATE TABLE content(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
description VARCHAR(1000));

CREATE TABLE tag_content_rel(
rel_id INT PRIMARY KEY AUTO_INCREMENT,
tag_id INT NOT NULL,
content_id INT NOT NULL);

其中 tag_content_rel 表中的 tag_id 对应 tag 表中的 id ，tag_content_rel 表中的 content_id 对应 content 表中的 id 。

再假设第一个表和第二个表都填充了足够数量的数据。现在请编写一个 MariaDB 过程，将第三个表填充为类似下面的数据：

rel_id,tag_id,content_id
0,0,0
1,1,0
2,2,0
3,3,0
4,4,1
5,1,1
6,2,1
7,3,1
8,5,2
9,1,2
10,2,2
11,3,2
12,1,3
13,3,3
14,4,3
15,5,3
16,0,4
17,2,4
18,3,4
19,4,4
20,10,5
21,16,5
22,17,5
23,18,5
24,11,6
25,16,6
26,17,6
27,18,6
28,12,7
29,16,7
30,17,7
31,18,7
32,10,8
33,11,8
34,17,8
35,18,8

简单说就是将 tag_content_rel 表填充为每个 content 都有一些 tag 相似的 content 。tag 相似的 content 为一组。设计出的 MariaDB 过程可以通过一些参数调整一共有多少组 content ，每个 content 有多少个 tag ，每组 content 有多少个 content 。

请考虑一下写入数据库的效率，写入速度越快越好，最好可以最大化利用 SSD 。要么就是写入过程可以暂停和恢复，这个应该是没法实现的吧？分段写入也可以，通过参数决定写入多少。最好是几天时间能写入一千万行吧。如果没必要的话更少也可以。

另外有没有可能用两个极端的测试数据集来模拟上面的测试数据？比如一个测试数据集 tag_id 和 content_id 都是完全随机的，而另外一个测试数据集每个 tag_id 和 content_id 组合都会重复若干次。这两个测试数据集都是在两个数据库中的，分别进行查询性能测试。如果二者查询性能都可以的话，是否就能确定我上面列的数据例子对应的模式的查询性能也是可以的？另外第二个有没有什么方法让每个 tag_id 和 content_id 组合的实际位置是随机的？我猜如果位置是有规律的可能查询性能会更好。完全随机的数据集我已经试过了，查询性能我比较满意。每个组合都重复若干次并且位置不固定我不会写。这个我好像没问过 ChatGPT 。

如果感觉太麻烦的话，我可以付 30 元钱。穷，更多给不起。不过能白嫖我肯定是更愿意白嫖的，我穷我不要脸。

[johntang824]

navicat 有生成数据的功能

[dddd1919]

先替你司老板捏把汗

[shendaowu]

@dddd1919 #2 我很好奇，你为什么这么说呢？

[shendaowu]

@johntang824 #1 大佬是给我一个探索的方向还是确定我的问题可以用 navicat 解决？我看了一下 navicat 数据生成的简介，好像没提供复杂的数据生成的工具。

[wangxin3]

先解决表 1 表 2 到表 3 的问题 https://mariadb.com/kb/en/insert-select/

[iyiluo]

需求太多，拆解一下再问 gpt

[levintrueno]

我是写 Java 的，我想我会写代码去生成...

[lyy16384]

测试数据为什么会考虑速度和暂停恢复啊，而且几天写一千万行也太太太小看数据库了

[yb2313]

自己写脚本生成应该可以

[johntang824]

@shendaowu 啊哈，我不是大佬，只是我感觉这个问题可以用 navicat 解决。如果我记得没错，那个数据生成的工具确实可以解决你上述的问题，是不是先生成 tag 和 content 数据，然后 tag_content_rel 数据的其中两个字段从上面两个表随机取，这个表(tag_content_rel)的主键生成即可。

[meshell]

op 试试我这个能不能生成，https://loocode.com/tool/sql-table-data-generator

[qindan]

我觉得 GPT 是能够解决你的问题的，就看你姿势对不对，

[hackhu2019]

我的想法是脚本生成 3 张表对应的 SQL 语句，至于表数据之间的关联关系在生成 SQL 文件的时候就处理好，然后数据库直接执行 SQL 文件，即使几千万的数据应该也是一小时内就搞定了

[hackhu2019]

给你一个参考的 prompts:
假设有如下三个 MariaDB 表：#表结构省略

其中 tag_content_rel 表中的 tag_id 对应 tag 表中的 id ，tag_content_rel 表中的 content_id 对应 content 表中的 id 。
现在我想填充 tag_content_rel 表数据，每行的数据 tag_id 随机从 tag 表获取，content_id 从 content 表获取，请帮我编写一个存储过程，需要 tag_content_rel 包含所有的 tag.id 和 content.id ，考虑写入数据库的效率，需要支持分段写入

[treblex]

laravel 对这块的定义是 seeding 吧,其他的框架很少见做这个的
非要用 gpt 的话，你要先告诉他怎么定义数据，让他生成有关系的结构化数据给你，然后再转 sql

[encro]

mysql 通过存储过程以及事务批量生成数据

https://c4ys.com/archives/858

很早的一篇文章了，希望对你有用

[pkoukk]

一天写 1000W 行，每秒写入才 115 ，4c8g 的虚机应该都扛得住，没啥好测的
测查询性能，自己写个两段代码写个百八十万行，explain 一下查询语句，看看索引使用正不正确就完事了
剩下的事情，就算要调优也不是你考虑的

[rlds]

你这个需求，navicat 完全能胜任了。

[MoYi123]

几天时间能写入一千万行, 还要考虑利用硬件?
不搞特殊优化的测试数据导入速度是 1 秒 10-20 万. 一千万也就 1 分钟左右.

[shendaowu]

@johntang824 #10
@rlds #18

我下载 navicat 了，那个数据生成功能感觉太简单了。好像没有复杂的选项。我感觉我的需求还是挺复杂的。

[meeop]

1 这个事不适合拿存储过程干,让 ai 写脚本,比如用 python 写,其中 python 可以不直接操作数据库,而是生成 sql,然后你只要导入生成的 sql,导入数据是非常快的,一秒应该能导入 1-10w 行,1000w 行数据也就是 2 分钟,你可以反复折腾

2 看你的需求,无非是给内容挂标签然后测试关联查询性能
这没啥好测试的,这数据关系型数据库最基础能力,只要加好索引,确认执行计划使用索引即可

[encro]

为啥我的 gptchat 生成我感觉还挺靠谱：


https://chatgpt.com/share/e9ae8558-94f8-404d-87cb-089c019fe229

[shendaowu]

@meeop #21 好的，我试试用 Python 。我的需求跟普通的标签有点区别，一次可能需要查询几百的标签，而且大部分查询可能都需要查几百个标签。我之前试过，查询的标签越多好像查询时间越长。

[zouri]

https://github.com/joke2k/faker ，python

[h0099]

首先 19 个月过去了阁下还在折腾这个 https://en.wikipedia.org/wiki/Many-to-many_(data_model) 关系的 https://en.wikipedia.org/wiki/Associative_entity ？并试图继续滥用`提前优化` https://z.n0099.net/#narrow/near/93295 思维精神？
/t/908231
/t/908246
/t/909074

> 要么就是写入过程可以暂停和恢复，这个应该是没法实现的吧？

why not? 合理假设您是在 https://en.wikipedia.org/wiki/Cunningham's_Law

> 分段写入也可以，通过参数决定写入多少

这跟前面的可暂停本质上是一样的

> 写入速度越快越好，最好可以最大化利用 SSD

建议直接`LOAD csv` https://dev.mysql.com/doc/refman/8.4/en/load-data.html
并在写入前后开关 redolog https://dev.mysql.com/doc/refman/8.4/en/innodb-redo-log.html#innodb-disable-redo-logging

> 最好是几天时间能写入一千万行吧。如果没必要的话更少也可以。

一千万行？一千万亿行！

---
```sql
CREATE TABLE tag_content_rel(
rel_id INT PRIMARY KEY AUTO_INCREMENT,
tag_id INT NOT NULL,
content_id INT NOT NULL);
```
为什么不`UNIQUE(tag_id, content_id)`？还是说您的确需要允许出现重复的`(tag_id, content_id)`对?

> 我猜如果位置是有规律的可能查询性能会更好

对于将 PK 用做 clustered index （详见 https://github.com/n0099/open-tbm/issues/48#issuecomment-2091811880 的`12.1`）的 mysql innodb storage engine 中的表`tag_content_rel`只有`PRIMARY KEY(rel_id, tag_id, content_id)`后才会符合您的假设