mysql学习笔记之数据引擎

查看当前数据库支持的引擎

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)

或者

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)

engine 引擎的名称
support 是否支付yes表示支持，no表示不支持
comment 评价或者备注 defalut表示，默认支持的引擎
transactions 是否支持事务，yes表示支持，no表示不支持
xa 所有支持的分布式是否符合xa规范，yes表示支持，no表示不支持
savepoints 是否支持事务处理中的保存点，yes表示支持，no表示不支持

或者

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 引擎名称
value 是否支持yes支持，no不支持,disabled表示支持但未启用

查看默认引擎

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 为默认引擎

修改默认引擎

my.ini文件

[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 默认端口号
port=3306
# path to installation directory. all paths are usually resolved relative to this. 服务器的默认安装目录
# basedir="c:/program files/mysql/mysql server 5.7/"
# path to the database root  数据库数据文件的目录
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 修改服务器默认字符
character-set-server=utf8
# the default storage engine that will be used when create new tables when
# 这里修改默认引擎
default-storage-engine=innodb

修改后重启mysql服务