View and modify the storage engine summary in MySQL, and view the storage engine in mysql

View and modify the storage engine summary in MySQL, and view the storage engine in mysql

This article summarizes some methods for viewing and modifying the storage engine in MySQL. The test and verification environment is MySQL 5.6. If there is any difference, refer to the actual version.

1: view the storage engine information of MySQL

1.1 Use the show engines command.

. Csharpcode ,. csharpcode pre {font-size: small; color: black; font-family: Las LAS, "Courier New", courier, monospace; background-color: # ffffff; /* white-space: pre ;*/}. csharpcode pre {margin: 0em ;}. csharpcode. rem {color: #008000 ;}. csharpcode. kwrd {color: # 0000ff ;}. csharpcode. str {color: #006080 ;}. csharpcode. op {color: # effecc0 ;}. csharpcode. preproc {color: # cc6633 ;}. csharpcode. asp {background-color: # ffff00 ;}. csharpcode. html {color: #800000 ;}. csharpcode. attr {color: # ff0000 ;}. csharpcode. alt {background-color: # f4f4f4; width: 100%; margin: 0em ;}. csharpcode. lnum {color: #606060 ;}

Support column. YES indicates that the current version supports this storage engine. DEFAULT indicates that this engine is the DEFAULT engine. NO indicates that the storage engine is not supported. As shown below, InnoDB is the default storage engine.

1.2 check the system variables default_storage_engine or storage_engine.

1: default_storage_engine indicates the default storage engine of the permanent Table (permanent tables.

2: default_tmp_storage_engine indicates the default storage engine of the temporary table.

Storage_engine is not recommended for use. It has been replaced by the system variable default_storage_engine.

 

mysql> show variables like '%storage_engine%';

+----------------------------+--------+

| Variable_name              | Value  |

+----------------------------+--------+

| default_storage_engine     | InnoDB |

| default_tmp_storage_engine | InnoDB |

| storage_engine             | MyISAM |

+----------------------------+--------+

3 rows in set (0.01 sec)

mysql> 

2: how to modify the default storage engine of MySQL?

2.1 modify my. cnf, add the default-storage-engine parameter in the configuration file, and restart the database service.

[Mysqld]

Default-storage-engine = MyISAM

 

Check the default storage engine and you will see that MyISAM is the default storage engine.

Mysql> SELECT TABLE_SCHEMA,

    ->        TABLE_NAME, 

    ->        TABLE_TYPE, 

    ->        ENGINE 

    -> FROM   information_schema.TABLES 

    -> WHERE  TABLE_NAME = 'TEST'; 

+--------------+------------+------------+--------+

| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |

+--------------+------------+------------+--------+

| gsp          | TEST       | BASE TABLE | InnoDB |

+--------------+------------+------------+--------+

1 row in set (0.00 sec)

mysql> 

3.2 run the show create table test \ G command.

 

mysql> SHOW CREATE TABLE TEST \G;

*************************** 1. row ***************************

       Table: TEST

Create Table: CREATE TABLE `TEST` (

  `ID` int(11) DEFAULT NULL,

  `COL1` varchar(6) DEFAULT NULL,

  `COL2` varchar(6) DEFAULT NULL,

  `COL3` varchar(6) DEFAULT NULL,

  `COL4` varchar(6) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ERROR: 

No query specified

mysql> 

3.3 SHOW TABLE STATUS

mysql> SHOW TABLE STATUS WHERE Name='TEST';

ERROR 1046 (3D000): No database selected

mysql> 

mysql> 

mysql> use gsp;

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 TABLE STATUS WHERE Name='TEST';

As shown in the preceding figure, you must develop a database. Otherwise, you can only use the following syntax:

Show table status [{FROM | IN}Db_name]

[LIKE'Pattern'| WHEREExpr]

Mysql> show table status from gsp WHERE Name = 'test ';

4: How do I modify the storage engine of related tables?

The storage engine for modifying tables is very simple, and the syntax is as follows:

Alter table my_table ENGINE = InnoDB;

mysql> ALTER TABLE TEST ENGINE=MyISAM;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE TEST \G;

*************************** 1. row ***************************

       Table: TEST

Create Table: CREATE TABLE `TEST` (

  `ID` int(11) DEFAULT NULL,

  `COL1` varchar(6) DEFAULT NULL,

  `COL2` varchar(6) DEFAULT NULL,

  `COL3` varchar(6) DEFAULT NULL,

  `COL4` varchar(6) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ERROR: 

No query specified

mysql> 

5. How to specify the storage engine when creating a table.

When creating a table, if you want to specify the storage ENGINE, you only need to set the parameter ENGINE. Very simple.

 

mysql> CREATE TABLE TEST1 (ID INT) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

mysql>  CREATE TABLE TEST2 (ID INT) ENGINE=MyISAM;

Query OK, 0 rows affected (0.00 sec)

References:

Https://dev.mysql.com/doc/refman/5.6/en/storage-engine-setting.html

Https://dev.mysql.com/doc/refman/5.7/en/storage-engine-setting.html