Dyntex.net Support Centre: MySQL Reference Manual p.503

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

Chapter 7: MySQL Table Types 503 CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2"); CREATE TABLE total (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; Note that you can also manipulate the `.MRG' le directly from the outside of the MySQL server: shell> cd /mysql-data-directory/current-database shell> ls -1 t1.MYI t2.MYI > total.MRG shell> mysqladmin flush-tables Now you can do things like: mysql> SELECT * FROM total; +---+---------+ | a | message | +---+---------+ | 1 | Testing | | 2 | table | | 3 | t1 | | 1 | Testing | | 2 | table | | 3 | t2 | +---+---------+ Note that the a column, though declared as PRIMARY KEY, is not really unique, as MERGE table cannot enforce uniqueness over a set of underlying MyISAM tables. To remap a MERGE table you can do one of the following: DROP the table and re-create it Use ALTER TABLE table_name UNION(...) Change the `.MRG' le and issue a FLUSH TABLE on the MERGE table and all underlying tables to force the handler to read the new denition le. 7.2.1 MERGE Table Problems The following are the known problems with MERGE tables: A MERGE table cannot maintain UNIQUE constraints over the whole table. When you do INSERT, the data goes into the rst or last table (according to INSERT_METHOD=xxx) and this MyISAM table ensures that the data are unique, but it knows nothing about others MyISAM tables. DELETE FROM merge_table used without a WHERE will only clear the mapping for the table, not delete everything in the mapped tables. RENAME TABLE on a table used in an active MERGE table may corrupt the table. This will be xed in MySQL 4.0.x. Creation of a table of type MERGE doesn't check if the underlying tables are of compatible types. If you use MERGE tables in this fashion, you are very likely to run into strange problems.

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide