Data Engine for mysql Study Notes
- 2021-07-09 09:24:47
- OfStack
View the engines supported by the current database
show engines
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
Or
show engines \G
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: 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: YES
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)
The name of the Engine engine
Whether Support pays YES is supported, NO is not supported
Comment evaluation or comment Defalut indicates that the engine supported by default
Whether Transactions supports transactions, YES supports transactions, and NO does not support transactions
Whether all supported distributions in XA comply with XA specification, YES supports and NO does not support
Whether Savepoints supports a savepoint in a transaction, YES supports it, and NO does not
Or
show variables like ‘have%'
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)
Variable_name Engine Name
Whether value supports YES, NO does not, DISABLED supports but is not enabled
View the default engine
show variables like ‘%storage_engine%'
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.00 sec)
InnoDB is the default engine
Modify the default engine
my. ini file
[mysqld]
# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory
# shared-memory-base-name=MYSQL
# The Pipe the MySQL Server will use
# socket=MYSQL
# The TCP/IP Port the MySQL Server will listen on Default port number
port=3306
# Path to installation directory. All paths are usually resolved relative to this. Default installation directory for the server
# basedir="C:/Program Files/MySQL/MySQL Server 5.7/"
# Path to the database root Directory of database data files
datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data
# The default character set that will be used when a new schema or table is
# created and no character set is defined Modify server default characters
character-set-server=utf8
# The default storage engine that will be used when create new tables when
# Modify the default engine here
default-storage-engine=INNODB
Restart Mysql service after modification