一、本文说明
由于刚学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: 0 mysql> 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: 0 mysql> 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: 0 mysql> select count(1) from t; +----------+ | count(1) | +----------+ | 16777216 | +----------+ 1 row in set (0.00 sec) [root@rhel5 test]# ll -h total 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 t Compressing t.MYD: (16777216 records) - Calculating statistics - Compressing file 85.71% [root@rhel5 test]# ll -h total 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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 stop Shutting down MySQL............... [ OK ] [root@rhel5 jack]# ll total 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 start Starting MySQL......................... [ OK ] [root@rhel5 mysql5.5]# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.5.22 Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_jack | +----------------+ | echo | +----------------+ 1 row in set (0.00 sec) mysql> use echo 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> 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: 0 mysql> 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 | +----+------+-----------+------+---------+-------+-------+------------------+