一、本文说明
由于刚学mysql所以动手做了一些实验。
二、实验内容
1、验证MyISAM有AUOT_INCREMENT coloumn功能
----在这里是对现有表t，增加一个主键---- mysql> alter table t add column id1 int not null auto_increment,add primary key(id1);Query OK, 3 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> desc t;+-------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+----------------+| id | int(11) | YES | | NULL | || id1 | int(11) | NO | PRI | NULL | auto_increment |+-------+---------+------+-----+---------+----------------+2 rows in set (0.00 sec)----当没有主键没有插入数据时，已经运行自动增长列了---- mysql> select * from t;+------+-----+| id | id1 |+------+-----+| 1 | 1 || 1 | 2 || 1 | 3 |+------+-----+3 rows in set (0.00 sec)
2、验证MyISAM表没有事务性
mysql> show variables like '%autocommit%';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.00 sec)mysql> set session autocommit=off;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%autocommit%';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+1 row in set (0.00 sec)mysql> insert into t value(1);Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+| id |+------+| 1 |+------+1 row in set (0.00 sec)mysql> rollback;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select * from t;+------+| id |+------+| 1 |+------+1 row in set (0.00 sec)
3、验证MyISAM的压缩性
mysql> insert into t values(1);Query OK, 1 row affected (0.00 sec)mysql> insert into t select * from t;Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> insert into t select * from t;Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0。。。。mysql> insert into t select * from t;Query OK, 8388608 rows affected (13.54 sec)Records: 8388608 Duplicates: 0 Warnings: 0mysql> select count(1) from t;+----------+| count(1) |+----------+| 16777216 |+----------+1 row in set (0.00 sec)[root@rhel5 test]# ll -htotal 113M-rw-rw---- 1 mysql mysql 8.4K Sep 3 13:53 t.frm-rw-rw---- 1 mysql mysql 112M Sep 3 14:05 t.MYD-rw-rw---- 1 mysql mysql 1.0K Sep 3 14:05 t.MYI[root@rhel5 test]# myisampack tCompressing t.MYD: (16777216 records)- Calculating statistics- Compressing file85.71% [root@rhel5 test]# ll -htotal 17M-rw-rw---- 1 mysql mysql 8.4K Sep 3 13:53 t.frm-rw-rw---- 1 mysql mysql 17M Sep 3 14:05 t.MYD-rw-rw---- 1 mysql mysql 1.0K Sep 3 14:06 t.MYI
备注：压缩完以后切记将表进行备份。
4、对MyISAM拷贝复制的例子mysql> use jack;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_jack |+----------------+| echo || jack |+----------------+2 rows in set (0.00 sec)mysql> create database echo;Query OK, 1 row affected (0.00 sec)mysql> exit;Bye[root@rhel5 mysql5.5]# service mysql stopShutting down MySQL............... [ OK ][root@rhel5 jack]# lltotal 36-rw-rw---- 1 mysql mysql 61 Sep 1 16:41 db.opt-rw-rw---- 1 mysql mysql 8556 Sep 4 10:19 echo.frm-rw-rw---- 1 mysql mysql 8556 Sep 4 10:18 jack.frm-rw-rw---- 1 mysql mysql 28 Sep 5 11:27 jack.MYD-rw-rw---- 1 mysql mysql 1024 Sep 5 11:27 jack.MYI[root@rhel5 jack]# mv jack.* ../echo/[root@rhel5 mysql5.5]# service mysql startStarting MySQL......................... [ OK ][root@rhel5 mysql5.5]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.22 Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || echo || jack || mysql || performance_schema || test |+--------------------+6 rows in set (0.00 sec)mysql> use jack;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_jack |+----------------+| echo |+----------------+1 row in set (0.00 sec)mysql> use echoReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_echo |+----------------+| jack |+----------------+1 row in set (0.00 sec)mysql> select * from jack;+------+| id |+------+| 1 || 2 || 1 || 2 |+------+4 rows in set (0.00 sec)
5、表的导入和导出
mysql> select * from jack;+------+| id |+------+| 1 || 2 |+------+2 rows in set (0.00 sec)mysql> select * from jack into outfile '/tmp/a.txt' fields terminated by ',' enclosed by '"';Query OK, 2 rows affected (0.00 sec)[root@rhel5 mysql5.5]# cat /tmp/a.txt"1""2"mysql> load data infile '/tmp/a.txt' into table jack fields terminated by ',' enclosed by '"';Query OK, 2 rows affected (0.00 sec)Records: 2 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from jack;+------+| id |+------+| 1 || 2 || 1 || 2 |+------+4 rows in set (0.00 sec)
6、杀掉mysql sleep进程的shell
mysql> show processlist;+----+------+-----------+------+---------+-------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+-------+-------+------------------+| 1 | root | localhost | test | Sleep | 72565 | | NULL || 2 | root | localhost | test | Sleep | 12145 | | NULL || 3 | root | localhost | test | Sleep | 12079 | | NULL || 6 | root | localhost | jack | Query | 0 | NULL | show processlist |+----+------+-----------+------+---------+-------+-------+------------------+4 rows in set (0.00 sec)----其中id为1、2、3的进程已经为sleep，先杀掉id=1[root@rhel5 ~]# mysqladmin kill 1[root@rhel5 ~]# mysqladmin processlist+----+------+-----------+------+---------+-------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+-------+-------+------------------+| 2 | root | localhost | test | Sleep | 12432 | | || 3 | root | localhost | test | Sleep | 12366 | | || 6 | root | localhost | jack | Sleep | 287 | | || 11 | root | localhost | | Query | 0 | | show processlist |+----+------+-----------+------+---------+-------+-------+------------------+