在 MySQL 中对数据库表进行分区
我正在编写一个数据仓库,使用 MySQL 作为后端。我需要根据两个整数 ID 和一个名称字符串对表进行分区。
一个更具体的例子是假设我正在存储有关学校的数据。我想根据以下内容基于复合“键”对 school_data 表进行分区:
school id (integer)
course_id (integer)
student_surname (string)
对于学生姓氏,姓氏的第一个字符决定数据应进入哪个“分区表”。
我如何使用 MySQL (5.1) 和 InnoDb 表来实现此要求?
另外,我正在 Windows 机器上进行开发,但我将部署到 *nix 机器上进行生产。我还有两个问题:
- 我假设从 Windows 迁移到 Linux 时必须转储和恢复数据。我不知道如果数据库包含分区表,这是否可以(指向文档中说明这一点的位置的指针将使我放心 - 我无法找到有关分区表的转储/恢复的任何具体提及。
- 我可能还需要更改数据库(如果 Oracle 对 MySQL 用户采取意外行动),在这种情况下,我将需要以某种方式将数据导出到另一个数据库中(希望这种情况不太可能发生)——转储数据的最佳方法是什么。从MySQL(也许是文本或其他东西)中记住分区表?
I am writing a data warehouse, using MySQL as the back-end. I need to partition a table based on two integer IDs and a name string.
A more concrete example would be to assume that I am storing data about a school. I want to partition the school_data table based on COMPOSITE 'Key' based on the following:
school id (integer)
course_id (integer)
student_surname (string)
For the student surname, it is just the first character of the surname that determines which 'partitioned table' the data should go in to.
How may I implement this requirement using MySQL (5.1) with InnoDb tables?
Also, I am doing my development on a Windows box, but I will deploy onto a *nix box for production. I have two further questions:
- I am assuming that I will have to dump and restore the data when moving from Windows to Linux. I don't know if this is OK if the database contains partitioned tables (pointer to where it states this in the documentation will put my mind to rest - I have not been able to find any specific mention of dump/restore regarding partitioned tables.
- I may also need to change databases (if Oracle pulls a surprise move on MySQL users) in which case I will need to SOMEHOW export the data into another database. In this (hopefully unlikely scenario) - what will be the best way to dump data out of MySQL (maybe to text or something) bearing in mind the partitioned table?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
RANGE 分区
按范围分区的表的分区方式是,每个分区都包含分区表达式值位于给定范围内的行。
范围分区
数据迁移到另一个数据库
MySQLDUMP 将输出将表和数据保存到文件中。但是,Oracle 支持通过ODBC连接到其他数据库,就像SQL Server具有链接服务器功能一样。
附录
可以按三列分区,但我的示例是根据您在OP中的要求:
RANGE Partitioning
A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.
Range partitioning
Data Migration to Another DB
MySQLDUMP will output the table and data to a file. However, Oracle supports connecting to other databases via ODBC, just as SQL Server has it's linked server capability.
Addendum
Partitioning by three columns is possible, but my example is per your requirements in the OP:
查看 MySQL 文档的 第 18 章分区特别是 分区类型 (我会看看在
HASH
分区)。但请记住,MySQL 5.1 中的分区实现仍在开发中,并且存在一些 限制和限制。我没有在 18.3 分区管理 中找不到任何内容,但是,根据这篇文章,支持备份和恢复分区表没什么特别的。
不过,
我会做一些测试。
Oracle SQL Developer 通过包含以下内容来整合迁移支持重新开发了功能并极大地扩展了原始 Oracle 迁移工作台提供的功能和可用性,以将 Microsoft Access、Microsoft SQL Server、MySQL 和 Sybase 数据库迁移到 Oracle。
Have a look at the Chapter 18. Partitioning of MySQL documentation and especially the Partition Types (I'd look at the
HASH
partitioning). But keep in mind that the partitioning implementation in MySQL 5.1 is still undergoing development and there are some limitations and restrictions.I didn't find anything in 18.3 Partition Management but, according to this post, backing up and restoring a partitioned table is nothing special. To backup:
And to restore:
I would do some testing though.
Oracle SQL Developer incorporates migration support by including redeveloped features and greatly extending the functionality and usability offered by the original Oracle Migration Workbench to migrate Microsoft Access, Microsoft SQL Server, MySQL and Sybase databases to Oracle.