一、本文说明
由于刚学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 |
+----+------+-----------+------+---------+-------+-------+------------------+