Mysql learning notes Data Engine and mysql learning notes Engine

Mysql learning notes Data Engine and mysql learning notes Engine

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 \Gmysql> show engines \G*************************** 1. row ***************************   Engine: InnoDB   Support: DEFAULT   Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES     XA: YES Savepoints: YES*************************** 2. row ***************************   Engine: MRG_MYISAM   Support: YES   Comment: Collection of identical MyISAM tablesTransactions: NO     XA: NO Savepoints: NO*************************** 3. row ***************************   Engine: MEMORY   Support: YES   Comment: Hash based, stored in memory, useful for temporary tablesTransactions: 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 engineTransactions: NO     XA: NO Savepoints: NO*************************** 6. row ***************************   Engine: CSV   Support: YES   Comment: CSV storage engineTransactions: NO     XA: NO Savepoints: NO*************************** 7. row ***************************   Engine: ARCHIVE   Support: YES   Comment: Archive storage engineTransactions: NO     XA: NO Savepoints: NO*************************** 8. row ***************************   Engine: PERFORMANCE_SCHEMA   Support: YES   Comment: Performance SchemaTransactions: NO     XA: NO Savepoints: NO*************************** 9. row ***************************   Engine: FEDERATED   Support: NO   Comment: Federated MySQL storage engineTransactions: NULL     XA: NULL Savepoints: NULL9 rows in set (0.00 sec)

Engine name
Support: whether to pay YES indicates Support, NO indicates not Support
Comment comments or remarks Defalut indicates that the default supported Engine
Whether Transactions supports Transactions. YES indicates YES, and NO indicates NO.
Whether all distributed files supported by XA comply with XA specifications. YES indicates YES, and NO indicates NO.
Whether Savepoints supports saving points in transaction processing. YES indicates YES, and NO indicates NO.

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 or NO. DISABLED indicates YES but not enabled.

View 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 default Engine

My. ini file

[Mysqld] # The next three options are mutually exclusive to SERVER_PORT below. # skip-networking # enable-named-pipe # 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 = 3306 # Path to installation directory. all paths are usually resolved relative to this. default Server Installation Directory # basedir = "C:/Program Files/MySQL Server 5.7/" # Path to the database root database data file directory datadir = C: /ProgramData/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 to modify The default character- set-server = utf8 # The default storage engine that will be used when create new tables when # Here modify The default engine default-storage-engine = INNODB

Restart the Mysql service after modification.