mysql 学习记录（六）-- 查看引擎、myisam引擎、自增长、主外键关联、memory引擎、merge引擎

最新推荐文章于 2021-01-18 21:47:56 发布
bwshqh
最新推荐文章于 2021-01-18 21:47:56 发布
阅读量1.1k
点赞数
分类专栏： mysql mysql学习文章标签： mysql 数据库
本文链接：https://blog.csdn.net/bwshqh/article/details/48894797
版权
mysql 同时被 2 个专栏收录
26 篇文章 0 订阅
订阅专栏
mysql学习
26 篇文章 2 订阅
订阅专栏
1.查看引擎
mysql> show variables like 'table_type';
Empty set (0.00 sec)

mysql> show engines \G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
6 rows in set (0.00 sec)

mysql> show variables like 'have%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_compress        | YES   |
| have_crypt           | YES   |
| have_csv             | YES   |
| have_dynamic_loading | YES   |
| have_geometry        | YES   |
| have_innodb          | YES   |
| have_ndbcluster      | NO    |
| have_openssl         | NO    |
| have_partitioning    | YES   |
| have_profiling       | YES   |
| have_query_cache     | YES   |
| have_rtree_keys      | YES   |
| have_ssl             | NO    |
| have_symlink         | YES   |
+----------------------+-------+
14 rows in set (0.00 sec)

2.myisam引擎相关
mysql> use test1;
Database changed
mysql> create table ai(
    -> i bigint(20) not null auto_increment,
    -> primary key(i)
    -> ) engine=myisam default charset=gbk;
Query OK, 0 rows affected (0.01 sec)

mysql> create table country(
    -> country_id smallint unsigned not null auto_increment,
    -> country varchar(50) not null,
    -> last_update timestamp not null default current_timestamp on update current_timestamp, primary key(country_id)
    -> ) engine = innodb default charset=gbk;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table ai engine = innodb;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ai \G;
*************************** 1. row ***************************
       Table: ai
Create Table: CREATE TABLE `ai` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> create table myisam_char(name char(10)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into myisam_char values('abcde'),('abcde    '),('       abcde'),('       abcde        ');
Query OK, 4 rows affected, 2 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> select name,length(name) from myisam_char;
+------------+--------------+
| name       | length(name) |
+------------+--------------+
| abcde      |            5 |
| abcde      |            5 |
|        abc |           10 |
|        abc |           10 |
+------------+--------------+
4 rows in set (0.00 sec)

mysql> truncate myisam_char;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from myisam_char;
Empty set (0.00 sec)

mysql> insert into myisam_char values
    -> ('abcde'),
    -> ('abcde  '),
    -> ('  abcde'),
    -> ('  abcde  ');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from myisam_char;
+---------+
| name    |
+---------+
| abcde   |
| abcde   |
|   abcde |
|   abcde |
+---------+
4 rows in set (0.00 sec)

mysql> select name,length(name) from myisam_char;
+---------+--------------+
| name    | length(name) |
+---------+--------------+
| abcde   |            5 |
| abcde   |            5 |
|   abcde |            7 |
|   abcde |            7 |
+---------+--------------+
4 rows in set (0.00 sec)

3.自增长
mysql> use test1;
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> create table autoincre_demo
    -> ( i smallint not null auto_increment,
    -> name varchar(10),primary key(i)
    -> ) engine = innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 1    |
| 2 | 2    |
| 3 | 3    |
+---+------+
3 rows in set (0.00 sec)

mysql> insert into autoincre_demo values(4,'4');
Query OK, 1 row affected (0.01 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into autoincre_demo(name) values('5'),('6'),('7');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

mysql> alter table autoincre_demo rename autoincre_demo_old;
Query OK, 0 rows affected (0.02 sec)

mysql> create table autoincre_demo (d1 smallint not null auto_increment, d2 smallint not null, name varchar(10), index(d2,d1) ) engine = myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into autoincre_demo (d2,name) values (2,'2'), (3,'3'), (4,'4'), (2,'2'), (3,'3'), (4,'4');
Query OK, 6 rows affected, 1 warning (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 1

mysql> select * from autoincre_demo;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
|  1 |  2 | 2    |
|  1 |  3 | 3    |
|  1 |  4 | 4    |
|  2 |  2 | 2    |
|  2 |  3 | 3    |
|  2 |  4 | 4    |
+----+----+------+
6 rows in set (0.00 sec)

4.主外键关联
mysql> alter table country rename country_old;
Query OK, 0 rows affected (0.02 sec)

mysql> create table country( country_id smallint unsigned not null auto_increment, country varchar(50) not null, last_update timestamp not null default current_timestamp on update current_timestamp, primary key( country_id) ) engine = innodb default charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> create table city( 
    -> city_id smallint unsigned not null auto_increment, 
    -> city varchar(50) not null, country_id smallint unsigned not null,
    -> last_update timestamp not null default current_timestamp on update current_timestamp,
    -> primary key(city_id), key idx_fk_country_id(country_id),
    -> constraint fk_city_country foreign key(country_id) references country(country_id)  on delete restrict on update cascade
    -> ) engine = innodb default charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into country(country_id,country) values (1,'tom');
Query OK, 1 row affected (0.01 sec)

mysql> select * from country where country_id =1;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|          1 | tom     | 2015-10-02 20:48:15 |
+------------+---------+---------------------+
1 row in set (0.00 sec)

mysql> insert into city(city_id,city,country_id) values ('251','bill',1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from city where country_id = 1;
+---------+------+------------+---------------------+
| city_id | city | country_id | last_update         |
+---------+------+------------+---------------------+
|     251 | bill |          1 | 2015-10-02 20:48:51 |
+---------+------+------------+---------------------+
1 row in set (0.00 sec)

mysql> update country set country_id = 10000 where country_id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from country where country='tom';
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|      10000 | tom     | 2015-10-02 20:49:29 |
+------------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from city where city_id = 251;
+---------+------+------------+---------------------+
| city_id | city | country_id | last_update         |
+---------+------+------------+---------------------+
|     251 | bill |      10000 | 2015-10-02 20:48:51 |
+---------+------+------------+---------------------+
1 row in set (0.00 sec)

mysql> show table status like 'city' \G
*************************** 1. row ***************************
           Name: city
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 252
    Create_time: 2015-10-02 20:47:27
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> show table status like 'country' \G
*************************** 1. row ***************************
           Name: country
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 2
    Create_time: 2015-10-02 20:42:25
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> desc country;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| country_id  | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| country     | varchar(50)          | NO   |     | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

mysql> select * from city;
+---------+------+------------+---------------------+
| city_id | city | country_id | last_update         |
+---------+------+------------+---------------------+
|     251 | bill |      10000 | 2015-10-02 20:48:51 |
+---------+------+------------+---------------------+
1 row in set (0.00 sec)

5.memory引擎
mysql> create table tab_memory engine = memory  select city_id,city,country_id from city group by city_id;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select count(*) from tab_memory;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> show table status like 'tab_memory' \G
*************************** 1. row ***************************
           Name: tab_memory
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 1
 Avg_row_length: 155
    Data_length: 127040
Max_data_length: 32505825
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-10-02 20:53:16
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> create index mem_hash using hash on tab_memory(city_id);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show index from tab_memory \G;
*************************** 1. row ***************************
        Table: tab_memory
   Non_unique: 1
     Key_name: mem_hash
 Seq_in_index: 1
  Column_name: city_id
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: HASH
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> drop index mem_hash on tab_memory;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create index mem_hash using btree on tab_memory(city_id);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show index from tab_memory \G
*************************** 1. row ***************************
        Table: tab_memory
   Non_unique: 1
     Key_name: mem_hash
 Seq_in_index: 1
  Column_name: city_id
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

6.merge引擎
mysql> use test1;
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> create table payment_2006( country_id smallint, payment_date datetime, amount decimal(15,2), key idx_fk_country_id(country_id) )engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table payment_2007( country_id smallint, payment_date datetime, amount decimal(15,2), key idx_fk_country_id(country_id) )engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> create table payment_all(
    -> country_id smallint,
    -> payment_date datetime,
    -> amount decimal(15,2),
    -> index(country_id)
    -> )engine=merge union=(payment_2006,payment_2007) insert_method=last;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into payment_2006 
    -> values(1,'2006-05-01',100000),
    -> (2,'2006-08-15',150000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into payment_2007
    -> values(1,'2007-02-20',35000),
    -> (2,'2007-07-15',220000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from payment_2006;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> select * from payment_2007;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
4 rows in set (0.00 sec)

mysql> insert into payment_all
    -> values(3,'2006-03-31',112200);
Query OK, 1 row affected (0.01 sec)

mysql> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
|          3 | 2006-03-31 00:00:00 | 112200.00 |
+------------+---------------------+-----------+
5 rows in set (0.00 sec)

mysql> select * from payment_2007;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
|          3 | 2006-03-31 00:00:00 | 112200.00 |
+------------+---------------------+-----------+
3 rows in set (0.00 sec)