MYSQL-合并表
原创woxuewangluo 博主文章分类:MYSQL文档 ©著作权
©著作权归作者所有:来自51CTO博客作者woxuewangluo的原创作品,请联系作者获取转载授权,否则将追究法律责任
1、实验环境win及其mysql版本:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.25 |
+-----------+
1 row in set (0.03 sec)
2、创建两张子表并建立主键:
mysql> create table t1 (a int not null primary key) engine=myisam;
Query OK, 0 rows affected (0.13 sec)
mysql> create table t2 (a int not null primary key) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
3、插入数据:
mysql> insert into t1(a) values(1),(2);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t2(a) values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
4、创建合并表:
mysql> create table mrg(a int not null primary key)
Query OK, 0 rows affected (0.08 sec)
注意:insert_method=last ---------------last将insert送入合并表中的最后一张表中
仅此两个选项
merge数据引擎:
5、合并表中插入数据:
mysql> select * from mgr;
ERROR 1146 (42S02): Table 'test.mgr' doesn't exist
mysql> select * from mrg;
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
+---+
4 rows in set (0.00 sec)
mysql> insert into mrg(a) values (3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mrg;
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
+---+
5 rows in set (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)
可以看出结果是正确的;
6、子表中插入数据:
mysql> insert into t2(a) values (4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
mysql> select * from mrg;
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
+---+
6 rows in set (0.00 sec)
mysql> insert into t1(a) values (5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mrg;
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
7 rows in set (0.00 sec)
可以看出合并表中同样拥有子表中的数据;
7、删除合并表:
mysql> select * from mrg;
ERROR 1146 (42S02): Table 'test.mrg' doesn't exist
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
| 2 |
| 5 |
+---+
3 rows in set (0.00 sec)
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)
mysql> insert into t1(a) values (6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2(a) values (6);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
| 2 |
| 5 |
| 6 |
+---+
4 rows in set (0.00 sec)
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+---+
5 rows in set (0.00 sec)
可以看出合并表没了,子表可以查看;
8、删除子表:
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mrg;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+---+
5 rows in set (0.00 sec)
mysql> insert into mrg(a) values (7);
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
可以看出删除了子表后合并表不能查询和写入。
上一篇:通过组策略禁ping
下一篇:浏览器清除缓存方法总结
![](https://ucenter.51cto.com/images/noavatar_middle.gif)
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
MySQL设置表自增步长
MySQL设置表自增步长的方法示例。
自增 MySQL sql -
mysql合并多个表
mysql合并多个表
mysql 数据库 sql Server SQL -
mysql如何合并表 mysql 合并
MySQL性能优化(一):MySQL架构与核心问题MySQL性能优化(二):选择优化的数据类型MySQL性能优化(三):深入理解索引的这点事 正确地创建和使用索引是实现高性能查询的基础。前面文章MySQL性能优化(三):深入理解索引的这点事已经介绍了各种类型的索引及其特点,而在实际使用索引中,如何真正有效地发挥索引的价值,还需要进一步持续不断地学习、磨练。接下来本篇文章将分享如何高效、正确的使
mysql如何合并表 mysql 列合并 mysql 合并列 mysql 合并查询结果 mysql 多列合并为一列