首页/应用软件/内容

mysql学习之数据引擎的示例代码分享

应用软件2022-12-11 阅读()
SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持SQL。
插件式存储引擎是MySQL数据库最重要的特征之一，用户可以根据应用的需要寻找如何存储和索引数据、是否使用事务等。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服务

以上就是mysql学习之数据引擎的示例代码分享的详细内容，更多请关注php中文网其它相关文章！


学习教程快速掌握从入门到精通的SQL知识。

……

标签:mysql学习之数据引擎的示例代码分享
相关阅读