欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql学习笔记之数据引擎

程序员文章站 2023-12-04 22:35:34
查看当前数据库支持的引擎 show engines +--------------------+---------+----------------------...

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

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服务