Database table partitioning in MySQL

Question

I am writing a data warehouse using MySQL as the source. I need to split a table based on two integer identifiers and a name string.

A more specific example is to assume that I am storing school data. I want to split the school_data table based on COMPOSITE 'Key' based on the following:

school id (integer)
course_id (integer)
student_surname (string)

For a student’s last name, this is only the first character of the last name, which determines which "partitioned table" the data should enter.

How can I implement this requirement using MySQL (5.1) with InnoDb tables?

In addition, I am developing in the Windows box, but I will expand in the * nix field for production. I have two more questions:

• I assume that I will have to dump and restore data when switching from Windows to Linux. I don’t know if this is normal if the database contains partitioned tables (a pointer to where it is indicated in the documentation will make me calm), I could not find a specific mention of a dump / restore regarding partitioned tables.
• I may also need to change the databases (if Oracle makes an unexpected move for MySQL users), in which case I will need to BUY export data to another database. In this (I hope, unlikely scenario) - what would be the best way to unload data from MySQL (possibly into text or something else), having in mind a partitioned table?

Answer 1

, , , , .

CREATE TABLE employees (
  school id (integer)
  course_id (integer)
  student_surname (string)
)
  PARTITION BY RANGE (student_surname) (
  PARTITION p0 VALUES LESS THAN ('ezzzzzzzzzzzzzzzzzzzzzzz'),
  PARTITION p1 VALUES LESS THAN ('ozzzzzzzzzzzzzzzzzzzzzzz'),
  PARTITION p2 VALUES LESS THAN ('tzzzzzzzzzzzzzzzzzzzzzzz'),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

MySQLDUMP . Oracle ODBC, SQL Server .

, , (, ). MySQL, 3 (int, int, string), .

, OP:

, , " " .

Answer 2

, mySQL (5.1) InnoDb?

18. MySQL ( HASH partitioning). , MySQL 5.1 , .

, Windows Linux. , , db ( , , ), / .

18.3 Partition Management, , , . :

mysqldump --opt db_name table_name > file.dump

:

mysql db_name < file.dump 

.

( Oracle mySQL), . (, ) - mySQL (, - ) ?

Oracle SQL Developer , , Oracle Migration Workbench, Microsoft Access, Microsoft SQL Server, MySQL Sybase Oracle.