I tried something similar just now
Here is MySQL for My PC
mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+------------------------------+
| VERSION_COMMENT | MySQL Community Server (GPL) |
| VERSION | 5.5.12-log |
| VERSION_COMPILE_MACHINE | x86 |
| DATADIR | C:\MySQL_5.5.12\data\ |
| VERSION_COMPILE_OS | Win64 |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)
I will run this using MyISAM
- Step 01) create a table called 'rolando'
- Step 02) insert 'dominique' and 'diamond'
- Step 03) copy the table structure to 'pamela'
- Step 04) alter 'pamela' to not have auto_increment
- Step 05) In DOS, copy rolando.MYD to pamela.MYD
- Step 06) run
REPAIR TABLE pamela;
(Rebuild pamela.MYI)
- Step 07) run
SELECT COUNT(1) FROM pamela;
- Step 08) run
SHOW CREATE TABLE pamela\G
- Step 09) run
SELECT * FROM pamela;
- Step 10) insert 'carlik' into pamela
- Step 11) run
SELECT * FROM pamela;
Let's see if these steps are kosher.
Here are Steps 1-4
mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)
mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)
mysql> create table rolando
-> (
-> name varchar(20),
-> id int not null auto_increment,
-> primary key (id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from rolando;
+-----------+----+
| name | id |
+-----------+----+
| dominique | 1 |
| diamond | 2 |
+-----------+----+
2 rows in set (0.00 sec)
mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)
mysql> show create table rolando\G
*************************** 1. row ***************************
Table: rolando
Create Table: CREATE TABLE `rolando` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table pamela\G
*************************** 1. row ***************************
Table: pamela
Create Table: CREATE TABLE `pamela` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table pamela\G
*************************** 1. row ***************************
Table: pamela
Create Table: CREATE TABLE `pamela` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
mysql>
Here is Step 6
C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
1 file(s) copied.
C:\>
Here are the rest of the Steps starting at Step 7
mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning | Number of rows changed from 0 to 2 |
| test.pamela | repair | status | OK |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)
mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from pamela;
+-----------+----+
| name | id |
+-----------+----+
| dominique | 1 |
| diamond | 2 |
| carlik | 3 |
+-----------+----+
3 rows in set (0.00 sec)
mysql>
Dangerous game, isn't it ???
Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:
The technique we are about to demonstrate is unsupported,
undocumented, and may not work. Use it at your risk. We advise you to
back up you data first!
I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?
You got guts, @atxdba !!!
If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.
You can create a single table that consumes no additional space except a .frm
file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.
Here is how you can do this:
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;
Using this method, you can query the 4 tables at the same time like this:
SELECT ReqF FROM XMerge WHERE EmpName='John';
Was that simple, or what ???
In your case, you have 75 tables. You would do this:
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';
The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:
ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';
Give it a Try !!!
Best Answer
fiddle