日韩亚洲欧美中文在线,天天干成人网,窝窝午夜精品一区二区

1、查看當(dāng)前數(shù)據(jù)庫(kù)支出的存儲(chǔ)引擎

方法1：

mysql> show engines \G;
*************************** 1. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)
ERROR:
No query specified

方法2：

（Value 顯示為“DISABLED”的記錄表示支持該存儲(chǔ)引擎，但是數(shù)據(jù)庫(kù)啟動(dòng)的時(shí)候被禁用。）

mysql> show variables like 'have%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| have_compress          | YES      |
| have_crypt             | NO       |
| have_dynamic_loading   | YES      |
| have_geometry          | YES      |
| have_openssl           | DISABLED |
| have_profiling         | YES      |
| have_query_cache       | YES      |
| have_rtree_keys        | YES      |
| have_ssl               | DISABLED |
| have_statement_timeout | YES      |
| have_symlink           | YES      |
+------------------------+----------+
11 rows in set, 1 warning (0.00 sec)

2、ENGINE={存儲(chǔ)引起類型} 創(chuàng)建表的時(shí)候，設(shè)置存儲(chǔ)引擎

mysql> create table a(
    -> i bigint(20) not null auto_increment,
    -> primary key (i)
    -> ) engine=myisam default charset=gbk;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: test
Query OK, 0 rows affected (1.33 sec)

3、alter able tablename engine={存儲(chǔ)引起類型} 修改表為其他存儲(chǔ)引擎

mysql> alter table a engine=innodb;
Query OK, 0 rows affected (1.70 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table a \G;
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.14 sec)

3.1 常用存儲(chǔ)引擎的對(duì)比

特點(diǎn)	MyISAM	InnoDB	MEMORY	MERGE	NDB
存儲(chǔ)限制	有	64TB	有	沒(méi)有	有
事務(wù)安全		支持
鎖機(jī)制	表鎖	行鎖	表鎖	表鎖	表鎖
B 樹(shù)索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
數(shù)據(jù)緩存		支持	支持		支持
索引緩存	支持	支持	支持	支持	支持
數(shù)據(jù)可壓縮	支持
空間使用	低	高	N/A	低	低
內(nèi)存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外鍵		支持

3.2 常用存儲(chǔ)引擎學(xué)習(xí)（MyISAM、InnoDB、MEMORY 和 MERGE）

MyISAM：

默認(rèn)的MySQL存儲(chǔ)引擎，不支持事務(wù)和外鍵

優(yōu)點(diǎn)：訪問(wèn)速度快

每個(gè)MyISAM在磁盤(pán)上存儲(chǔ)成3個(gè)文件，其文件名和表名都相同。擴(kuò)展名分別是：

.frm (存儲(chǔ)表定義)

.MYD (MYData，存儲(chǔ)數(shù)據(jù))

.MYI (MYIndex，存儲(chǔ)索引)

(數(shù)據(jù)文件和索引文件可以放置在不同的目錄，平均分布 IO，獲得更快的速度。)

InnoDB:

處理效率較差，占用較多的空間用來(lái)保留數(shù)據(jù)和索引

優(yōu)點(diǎn)：具有提交、回滾、奔潰恢復(fù)能力的事務(wù)安全、唯一支持外鍵的存儲(chǔ)引擎

自動(dòng)增長(zhǎng)列：InnoDB 表的自動(dòng)增長(zhǎng)列可以手工插入，但是插入的值如果是空或者 0，則實(shí)際插入的將是自動(dòng)增長(zhǎng)后的值

mysql> create table autoincre_demo(
    -> i smallint not null auto_increment,
    -> name varchar(10),primary key(i)
    -> )engine=innodb;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: test
Query OK, 0 rows affected (1.19 sec)
mysql> insert into autoincre_demo values(1,"121"),(0,"dddf"),(null,"fdf");
Query OK, 3 rows affected (0.59 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 121  |
| 2 | dddf |
| 3 | fdf  |
+---+------+
3 rows in set (0.00 sec)

alter table tabename auto_increment=n 設(shè)置自動(dòng)增長(zhǎng)列的初始值（此值默認(rèn)從1開(kāi)始）

可以使用 LAST_INSERT_ID()查詢當(dāng)前線程最后插入記錄使用的值。如果一次插入了多條記錄，那么返回的是第一條記錄使用的自動(dòng)增長(zhǎng)值。

下面的例子演示了使用 LAST_INSERT_ID()的情況：

mysql> insert into autoincre_demo(name) values('3');
Query OK, 1 row affected (0.36 sec)
mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               15 |
+------------------+
1 row in set (0.00 sec)
mysql> insert into autoincre_demo(name) values('3'),('6'),('323'),('21');
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               16 |
+------------------+
1 row in set (0.00 sec)

外鍵約束：

在創(chuàng)建外鍵的時(shí)候，要求父表必須有對(duì)應(yīng)的索引，子表在創(chuàng)建外鍵的時(shí)候也會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的索引。

下面是樣例數(shù)據(jù)庫(kù)中的兩個(gè)表，country 表是父表，country_id 為主鍵索引，city 表是子表，country_id 字段對(duì) country 表的 country_id 有外鍵。

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.86 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 (3.22 sec)

在創(chuàng)建索引的時(shí)候，可以指定在刪除、更新父表時(shí)，對(duì)子表進(jìn)行的相應(yīng)操作，包 RESTRICT、CASCADE、SET NULL 和 NO ACTION

RESTRICT 和 NO ACTION 相同，是指限制在子表有關(guān)聯(lián)記錄的情況下父表不能更新
CASCADE 表示父表在更新或者刪除時(shí)，更新或者刪除子表對(duì)應(yīng)記錄；
SET NULL 則表示父表在更新或者刪除的時(shí)候，子表的對(duì)應(yīng)字段被 SET NULL。

mysql> select * from country;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|          1 | AAA     | 2021-06-16 15:09:22 |
+------------+---------+---------------------+
1 row in set (0.00 sec)
mysql> select * from city;
+---------+------+------------+---------------------+
| city_id | city | country_id | last_update         |
+---------+------+------------+---------------------+
|      10 | bb   |          1 | 2021-06-16 15:11:45 |
+---------+------+------------+---------------------+
1 row in set (0.00 sec)
mysql> delete from country where country_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)
mysql> update country set country_id = 10000 where country_id = 1;
Query OK, 1 row affected (0.62 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from country;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|      10000 | AAA     | 2021-06-16 15:13:35 |
+------------+---------+---------------------+
1 row in set (0.00 sec)
mysql> select * from city
    -> ;
+---------+------+------------+---------------------+
| city_id | city | country_id | last_update         |
+---------+------+------------+---------------------+
|      10 | bb   |      10000 | 2021-06-16 15:11:45 |
+---------+------+------------+---------------------+
1 row in set (0.00 sec)

在導(dǎo)入多個(gè)表的數(shù)據(jù)時(shí)，如果需要忽略表之前的導(dǎo)入順序，可以暫時(shí)關(guān)閉外鍵的檢查；同樣，在執(zhí)行 LOAD DATA 和 ALTER TABLE 操作的時(shí)候，可以通過(guò)暫時(shí)關(guān)閉外鍵約束來(lái)加快處理的速度，關(guān)閉的命令是“SET FOREIGN_KEY_CHECKS = 0;”，執(zhí)行完成之后，通過(guò)執(zhí)行“SETFOREIGN_KEY_CHECKS = 1;”語(yǔ)句改回原狀態(tài)。

查看表外鍵信息：show create table 或者 show table status 命令

mysql> show table status like 'city' \G;
*************************** 1. row ***************************
           Name: city
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 11
    Create_time: 2021-06-16 15:02:17
    Update_time: 2021-06-16 15:13:35
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.43 sec)
ERROR:
No query specified

存儲(chǔ)方式：

　?。?）、使用共享表空間存儲(chǔ)：表的表結(jié)構(gòu)保存在.frm文件中，數(shù)據(jù)+索引存在 innodb_data_home_dir 和 innodb_data_file_path 定義的表空間中，可以是多個(gè)文件
　　（2）、使用多表空間存儲(chǔ)：表的表結(jié)構(gòu)也保存在.frm文件中，數(shù)據(jù)+索引單獨(dú)存在.ibd中；如果是分區(qū)表，則每個(gè)分區(qū)對(duì)應(yīng)單獨(dú)的.ibd文件，文件名是：“表名+分區(qū)名”，可以在創(chuàng)建分區(qū)的時(shí)候指定每個(gè)分區(qū)的數(shù)據(jù)文件的位置，以此來(lái)將表的 IO 均勻分布在多個(gè)磁盤(pán)上

MEMORY：

使用存在內(nèi)存中的內(nèi)容來(lái)創(chuàng)建表

每個(gè) MEMORY 表只實(shí)際對(duì)應(yīng)一個(gè)磁盤(pán)文件，格式是.frm。

優(yōu)點(diǎn)：訪問(wèn)速度快（數(shù)據(jù)存儲(chǔ)在內(nèi)存中），并且默認(rèn)使用HASH索引，服務(wù)關(guān)閉則數(shù)據(jù)丟失

mysql> CREATE TABLE tab_memory ENGINE=MEMORY
    -> SELECT city_id,city,country_id
    -> FROM city GROUP BY city_id;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    12
Current database: test
Query OK, 1 row affected (0.62 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from tab_memory;
+---------+------+------------+
| city_id | city | country_id |
+---------+------+------------+
|      10 | bb   |      10000 |
+---------+------+------------+
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: 520320
Max_data_length: 65011650
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-06-16 15:28:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

給表創(chuàng)建索引的時(shí)候可以指定索引類型是HASH或是BTREE

mysql> create index mem_hash using hash on tab_memory(city_id);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    13
Current database: test
Query OK, 1 row affected (0.63 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: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: HASH
      Comment:
Index_comment:
1 row in set (0.32 sec)
ERROR:
No query specified
mysql> drop index mem_hash on tab_memory;
Query OK, 1 row affected (0.31 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.16 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)
ERROR:
No query specified

MERGE：

此存儲(chǔ)殷勤是一組MyISAM表的組合

對(duì) MERGE 類型的表可以進(jìn)行查詢、更新、刪除的操作，這些操作實(shí)際上是對(duì)內(nèi)部的實(shí)際的 MyISAM 表進(jìn)行的。

對(duì)于 MERGE 類型表的插入操作，是通過(guò)INSERT_METHOD 子句定義插入的表，可以有 3 個(gè)不同的值，使用 FIRST 或 LAST 值使得插入操作被相應(yīng)地作用在第一或最后一個(gè)表上，不定義這個(gè)子句或者定義為 NO，表示不能對(duì)這個(gè) MERGE 表執(zhí)行插入操作。

可以對(duì) MERGE 表進(jìn)行 DROP 操作，這個(gè)操作只是刪除 MERGE 的定義，對(duì)內(nèi)部的表沒(méi)有任何的影響。

存儲(chǔ)文件：一個(gè).frm 文件存儲(chǔ)表定義，另一個(gè).MRG 文件包含組合表的信息，包括 MERGE 表由哪些表組成、插入新的數(shù)據(jù)時(shí)的依據(jù)

mysql> create table payment_2020(
    -> 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.25 sec)
mysql>  create table payment_2021(
    -> 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.54 sec)
mysql> CREATE TABLE payment_all(
    -> country_id smallint,
    -> payment_date datetime,
    -> amount DECIMAL(15,2),
    -> INDEX(country_id)
    -> )engine=merge union=(payment_2020,payment_2021) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.47 sec)

分別向2020和2021中插入數(shù)據(jù)，并查詢

mysql> insert into payment_2020 values(1,'2020-06-01',100000),(2,'2020-06-15',150000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> insert into payment_2021 values(1,'2021-04-20',35000),(2,'2021-06-15',220000);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from payment_2020;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2020-06-01 00:00:00 | 100000.00 |
|          2 | 2020-06-15 00:00:00 | 150000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)
mysql> select * from payment_2021;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2021-04-20 00:00:00 |  35000.00 |
|          2 | 2021-06-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)
mysql> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2020-06-01 00:00:00 | 100000.00 |
|          2 | 2020-06-15 00:00:00 | 150000.00 |
|          1 | 2021-04-20 00:00:00 |  35000.00 |
|          2 | 2021-06-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
4 rows in set (0.00 sec)

可以發(fā)現(xiàn)，payment_all 表中的數(shù)據(jù)是 payment_2020 和 payment_2021 表的記錄合并后的結(jié)果集

下面向 MERGE 表插入一條記錄，由于 MERGE 表的定義是 INSERT_METHOD=LAST，就會(huì)向最后一個(gè)表中插入記錄，所以雖然這里插入的記錄是 2006 年的，但仍然會(huì)寫(xiě)到 payment_2021表中。

mysql> insert into payment_all values(3,'2020-03-30',12333131);
Query OK, 1 row affected (0.31 sec)
mysql> select * from payment_all;
+------------+---------------------+-------------+
| country_id | payment_date        | amount      |
+------------+---------------------+-------------+
|          1 | 2020-06-01 00:00:00 |   100000.00 |
|          2 | 2020-06-15 00:00:00 |   150000.00 |
|          1 | 2021-04-20 00:00:00 |    35000.00 |
|          2 | 2021-06-15 00:00:00 |   220000.00 |
|          3 | 2020-03-30 00:00:00 | 12333131.00 |
+------------+---------------------+-------------+
5 rows in set (0.00 sec)
mysql> select * from payment_2021;
+------------+---------------------+-------------+
| country_id | payment_date        | amount      |
+------------+---------------------+-------------+
|          1 | 2021-04-20 00:00:00 |    35000.00 |
|          2 | 2021-06-15 00:00:00 |   220000.00 |
|          3 | 2020-03-30 00:00:00 | 12333131.00 |
+------------+---------------------+-------------+
3 rows in set (0.00 sec)
mysql> select * from payment_2020;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2020-06-01 00:00:00 | 100000.00 |
|          2 | 2020-06-15 00:00:00 | 150000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

到此這篇關(guān)于MySQL表類型存儲(chǔ)引擎的選擇的文章就介紹到這了,更多相關(guān)MySQL表類型存儲(chǔ)引擎內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。

相關(guān)文章

mysql學(xué)習(xí)筆記之表的基本操作

centos編譯安裝mysql 5.6及安裝多個(gè)mysql實(shí)例詳解

mysql 5.7.11 winx64.zip安裝配置方法圖文教程

mysql 5.7.17 winx64.zip安裝配置方法圖文教程

CentOS安裝mysql5.7 及簡(jiǎn)單配置教程詳解

MySQL 5.7 zip版本(zip版)安裝配置步驟詳解

MySQL5.6.31 winx64.zip 安裝配置教程詳解

MySQL注入繞開(kāi)過(guò)濾的技巧總結(jié)

一次Mysql死鎖排查過(guò)程的全紀(jì)錄

Windows10 64位安裝MySQL5.6.35的圖文教程