CREATE TABLE IF NOT EXISTS `user1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sex` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- select * from user1
CREATE TABLE IF NOT EXISTS `user2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sex` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- select * from user2
INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0);
INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);
CREATE TABLE IF NOT EXISTS `alluser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sex` int(1) NOT NULL DEFAULT '0',
INDEX(id)
) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;
如果是type=merge会有错误,最开始找的是这种方式,结果一直显示错误,找了好久,终于发现要用engine=merge
,这样才正确。
mysql> CREATE TABLE IF NOT EXISTS `alluser` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> INDEX(id)
-> ) ENGINE=MERGE UNION=(user1,user2) DEFAULT CHARSET=utf8 INSERT_METHOD=L
AST ;
Query OK, 0 rows affected (0.16 sec)
mysql> select id,name,sex from alluser;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | 张映 | 0 |
| 1 | tank | 1 |
+----+------+-----+
2 rows in set (0.00 sec)
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sex` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- select * from user1
CREATE TABLE IF NOT EXISTS `user2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sex` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- select * from user2
INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0);
INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);
CREATE TABLE IF NOT EXISTS `alluser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sex` int(1) NOT NULL DEFAULT '0',
INDEX(id)
) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;
如果是type=merge会有错误,最开始找的是这种方式,结果一直显示错误,找了好久,终于发现要用engine=merge
,这样才正确。
mysql> CREATE TABLE IF NOT EXISTS `alluser` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> INDEX(id)
-> ) ENGINE=MERGE UNION=(user1,user2) DEFAULT CHARSET=utf8 INSERT_METHOD=L
AST ;
Query OK, 0 rows affected (0.16 sec)
mysql> select id,name,sex from alluser;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | 张映 | 0 |
| 1 | tank | 1 |
+----+------+-----+
2 rows in set (0.00 sec)