mysql学习笔记之数据引擎
查看当前数据库支持的引擎
showengines +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ |Engine|Support|Comment|Transactions|XA|Savepoints| +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ |InnoDB|DEFAULT|Supportstransactions,row-levellocking,andforeignkeys|YES|YES|YES| |MRG_MYISAM|YES|CollectionofidenticalMyISAMtables|NO|NO|NO| |MEMORY|YES|Hashbased,storedinmemory,usefulfortemporarytables|NO|NO|NO| |BLACKHOLE|YES|/dev/nullstorageengine(anythingyouwritetoitdisappears)|NO|NO|NO| |MyISAM|YES|MyISAMstorageengine|NO|NO|NO| |CSV|YES|CSVstorageengine|NO|NO|NO| |ARCHIVE|YES|Archivestorageengine|NO|NO|NO| |PERFORMANCE_SCHEMA|YES|PerformanceSchema|NO|NO|NO| |FEDERATED|NO|FederatedMySQLstorageengine|NULL|NULL|NULL| +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9rowsinset(0.00sec)
或者
showengines\G mysql>showengines\G ***************************1.row*************************** Engine:InnoDB Support:DEFAULT Comment:Supportstransactions,row-levellocking,andforeignkeys Transactions:YES XA:YES Savepoints:YES ***************************2.row*************************** Engine:MRG_MYISAM Support:YES Comment:CollectionofidenticalMyISAMtables Transactions:NO XA:NO Savepoints:NO ***************************3.row*************************** Engine:MEMORY Support:YES Comment:Hashbased,storedinmemory,usefulfortemporarytables Transactions:NO XA:NO Savepoints:NO ***************************4.row*************************** Engine:BLACKHOLE Support:YES Comment:/dev/nullstorageengine(anythingyouwritetoitdisappears) Transactions:NO XA:NO Savepoints:NO ***************************5.row*************************** Engine:MyISAM Support:YES Comment:MyISAMstorageengine Transactions:NO XA:NO Savepoints:NO ***************************6.row*************************** Engine:CSV Support:YES Comment:CSVstorageengine Transactions:NO XA:NO Savepoints:NO ***************************7.row*************************** Engine:ARCHIVE Support:YES Comment:Archivestorageengine Transactions:NO XA:NO Savepoints:NO ***************************8.row*************************** Engine:PERFORMANCE_SCHEMA Support:YES Comment:PerformanceSchema Transactions:NO XA:NO Savepoints:NO ***************************9.row*************************** Engine:FEDERATED Support:NO Comment:FederatedMySQLstorageengine Transactions:NULL XA:NULL Savepoints:NULL 9rowsinset(0.00sec)
Engine引擎的名称
Support是否支付YES表示支持，NO表示不支持
Comment评价或者备注Defalut表示，默认支持的引擎
Transactions是否支持事务，YES表示支持，NO表示不支持
XA所有支持的分布式是否符合XA规范，YES表示支持，NO表示不支持
Savepoints是否支持事务处理中的保存点，YES表示支持，NO表示不支持
或者
showvariableslike‘have%'
mysql>showvariableslike'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| +------------------------+----------+ 11rowsinset,1warning(0.00sec)
Variable_name引擎名称
value是否支持YES支持，NO不支持,DISABLED表示支持但未启用
查看默认引擎
showvariableslike‘%storage_engine%'
mysql>showvariableslike'%storage_engine%'; +----------------------------------+--------+ |Variable_name|Value| +----------------------------------+--------+ |default_storage_engine|InnoDB| |default_tmp_storage_engine|InnoDB| |disabled_storage_engines|| |internal_tmp_disk_storage_engine|InnoDB| +----------------------------------+--------+ 4rowsinset,1warning(0.00sec)
InnoDB为默认引擎
修改默认引擎
my.ini文件
[mysqld] #ThenextthreeoptionsaremutuallyexclusivetoSERVER_PORTbelow. #skip-networking #enable-named-pipe #shared-memory #shared-memory-base-name=MYSQL #ThePipetheMySQLServerwilluse #socket=MYSQL #TheTCP/IPPorttheMySQLServerwilllistenon默认端口号 port=3306 #Pathtoinstallationdirectory.Allpathsareusuallyresolvedrelativetothis.服务器的默认安装目录 #basedir="C:/ProgramFiles/MySQL/MySQLServer5.7/" #Pathtothedatabaseroot数据库数据文件的目录 datadir=C:/ProgramData/MySQL/MySQLServer5.7\Data #Thedefaultcharactersetthatwillbeusedwhenanewschemaortableis #createdandnocharactersetisdefined修改服务器默认字符 character-set-server=utf8 #Thedefaultstorageenginethatwillbeusedwhencreatenewtableswhen #这里修改默认引擎 default-storage-engine=INNODB
修改后重启Mysql服务