8.MYISAM存储格式是轻便的,表可以直接从一个数据库复制到另外一个数据库。
下面做个实验,将tt1表从test数据库复制到test2数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2 |
+--------------------+
5 rows in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql> create table t1(id int) engine=myisam;
ERROR 1050 (42S01): Table 't1' already exists
mysql> create table tt1(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tt1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tt1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
必须干净的关闭mysql
[root@rhel131 ~]# service mysql stop
Shutting down MySQL..... [ OK ]
将数据文件都复制到test2目录下
[root@rhel131 /]# cd /usr/local/mysql/data/test
[root@rhel131 test]# cp tt1* ../test2/
[root@rhel131 test2]# chown -R mysql.mysql /usr/local/mysql/data/test2
[root@rhel131 data]# service mysql start
Starting MySQL................................ [ OK ]
再连接到test2,发现tt1表已复制过来了。
mysql> use test2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from tt1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
9.myisam可以指定表存储多少行的数据。
9.1 可以在表级别来指定表的最大和最小的行数,不过好像这种方式不是很有用。
mysql> create table t (id int,msg char(10)) max_rows=5 min_rows=2;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t values(1,'a');
Query OK, 1 row affected (0.02 sec)
。。。
mysql> insert into t values(1,'a');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t;
+------+------+
| id | msg |
+------+------+
| 1 | a |
| 1 | a |
| 1 | a |
| 1 | a |
| 1 | a |
| 1 | a |
| 1 | a |
| 1 | a |
| 1 | a |
+------+------+
9 rows in set (0.00 sec)
虽然指定最大的行数是5,我插入9笔数据还是可以的。
9.2 另一种方法就是在系统级别指定表的大小。
mysql> show global variables like '%myisam_data_pointer_size%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| myisam_data_pointer_size | 6 |
+--------------------------+-------+
1 row in set (0.00 sec)
默认值是6,表示一张表最大的大小是6的16次方,单位是字节。
为了便于测试,将值设为2,2的16次方为64k。
mysql> set global myisam_data_pointer_size=2;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 engine myisam as select * from information_schema.tables;
Query OK, 141 rows affected (0.06 sec)
Records: 141 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 141 rows affected (0.01 sec)
Records: 141 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
ERROR 1114 (HY000): The table 't1' is full
mysql> exit
Bye
[root@rhel131 test]# ls -lth
total 208K
-rw-rw---- 1 mysql mysql 64K Oct 30 18:09 t1.MYD
-rw-rw---- 1 mysql mysql 1.0K Oct 30 18:09 t1.MYI
-rw-rw---- 1 mysql mysql 18K Oct 30 18:09 t1.frm
-rw-rw---- 1 mysql mysql 96K Oct 30 17:42 t.ibd
-rw-rw---- 1 mysql mysql 8.4K Oct 30 17:40 t.frm
-rw-r--r-- 1 mysql mysql 65 Jul 11 00:17 db.opt
将超过64k时就报错了。