login
Python创建MySQL链表结构数据库表
2025/06/17·StepFun can make mistakes, check important info.
使用python mysql lib创建下面的数据库,id为自增,prev_chunk_id和next_chunk_id为其他chunk的id *id bigint Chunk id。primary key,按此字段的hash值投递到不同partition,以保证时序。 *document_id bigint Chunk所属Document id。 *knowledge_id bigint Chunk所属Knowledge id。 title string 内容标题,预留字段 *content mediumtext 内容,含多模态信息。 prev_chunk_id bigint 紧邻的上一个chunk id next_chunk_id bigint 紧邻的下一个chunk id file_name String 文件名 created_at datetime(3) 创建时间。 updated_at datetime(3) 更新时间。 deleted_at datetime(3) 删除时间。
Reason
思考过程

我们使用Python的MySQL库(如mysql-connector-python或pymysql)来创建数据库表。
根据要求,id是主键,并且我们希望按id的hash值进行分片(但分片是在应用层或数据库分片功能中处理的,在表结构定义中我们只需要定义主键)。
注意:在MySQL中,我们定义主键为id,并且设置为自增(AUTO_INCREMENT),但这里要求按id的hash值投递到不同partition,这个分片逻辑是在应用层处理的,在数据库表结构中我们只需要定义主键为id即可。
另外,prev_chunk_id和next_chunk_id是其他chunk的id,因此它们应该是bigint类型,并且允许为NULL(因为第一个chunk没有上一个,最后一个没有下一个)。

字段说明:
id: bigint, 主键,自增(AUTO_INCREMENT)
document_id: bigint, 非空
knowledge_id: bigint, 非空
title: varchar(255) 或 text,这里使用varchar(255)作为字符串类型,但考虑到标题可能较长,也可以使用text。不过根据需求,预留字段,我们使用varchar(255)应该足够。
content: mediumtext, 非空
prev_chunk_id: bigint, 可以为NULL
next_chunk_id: bigint, 可以为NULL
file_name: varchar(255) 或 text,这里使用varchar(255)
created_at: datetime(3) 表示精确到毫秒,在MySQL 5.6.4及以上版本支持,我们可以使用DATETIME(3)
updated_at: datetime(3)
deleted_at: datetime(3),允许为NULL,因为未删除时为NULL

注意:我们还需要设置默认的时间值,通常created_at和updated_at可以设置默认值,并且updated_at可以在更新时自动更新(但这里没有要求自动更新,所以我们可以选择手动更新,或者使用触发器。不过,为了简单,我们在应用层控制)。

另外,我们可能需要为document_id, knowledge_id等建立索引以提高查询效率,但根据要求,目前只需要创建表。

我们使用以下SQL语句创建表:

sql

注意:这里我们设置updated_at为在更新时自动更新为当前时间(精确到毫秒)。created_at只设置默认值。

但是,在MySQL中,一个表只能有一个TIMESTAMP或DATETIME列具有自动更新行为,而且我们这里有两个(created_at和updated_at)。然而,实际上我们可以设置两个,只要在updated_at上指定ON UPDATE即可。

另外,我们还需要考虑索引:
为了加速查询,我们可能会在document_id, knowledge_id, prev_chunk_id, next_chunk_id上建立索引。但是,创建表的时候可以单独创建索引。

不过,根据问题,我们只创建表,索引可以根据后续需求添加。但为了性能,我们可以先添加一些基本索引。

我们可以添加:
INDEX idx_document_id (document_id),
INDEX idx_knowledge_id (knowledge_id),
INDEX idx_prev_chunk_id (prev_chunk_id),
INDEX idx_next_chunk_id (next_chunk_id)

另外,由于prev_chunk_id和next_chunk_id可能会用于链表连接,所以索引很重要。

因此,完整的建表语句如下:

以下是使用Python的mysql-connector库创建该数据库表的完整代码:

python

关键设计说明:

  1. 主键与自增

    sql
    • 使用AUTO_INCREMENT实现自增ID
    • BIGINT类型支持海量数据
  2. 分片设计

    sql
    • 实际分片需在数据库集群层实现(如MySQL Cluster或ProxySQL)
    • 应用层可通过HASH(id)决定数据路由
  3. 链表结构

    sql
    • 支持双向链表结构
    • 单独索引优化邻居节点查询
  4. 时间戳

    sql
    • 精确到毫秒(3)
    • updated_at自动更新
  5. 多模态内容

    sql
    • 支持最大16MB内容(MEDIUMTEXT)
    • 可存储Base64编码的二进制/富文本

使用前需修改:

  1. 替换连接配置中的your_usernameyour_passwordyour_hostyour_database_name
  2. 确保MySQL版本≥5.6.4(支持DATETIME(3))
  3. 如需分片,需配置数据库集群(如Vitess或MyCat)

注意事项:

  1. 删除操作建议使用软删除(设置deleted_at
  2. 大文本字段单独存储时考虑使用LONGTEXT(最大4GB)
  3. 实际分片需在中间件层实现哈希路由
  4. 多模态数据存储建议结合对象存储(如OSS/S3)