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