How to modify the default storage engine in MySQL

mysql storage engine:

The MySQL server adopts a modular style, and each part remains relatively independent, especially in the storage architecture. The storage engine is responsible for managing data storage and MySQL index management. The MySQL server is able to communicate with the storage engine through a defined API. The most commonly used ones are MyISAM and InnoDB. After InnoDB was acquired by Oracle, MySQL's self-developed new storage engine Falcon will be introduced in MySQL version 6.0.

The MyISAM engine is a non-transactional engine that provides high-speed storage and retrieval, as well as full-text search capabilities, and is suitable for applications with frequent queries such as data warehouses. In MyISAM, a table is actually saved as three files: .frm stores table definition, .MYD stores data, and .MYI stores indexes.

InnoDB is an engine that supports transactions. All data is stored in one or more data files, supporting a locking mechanism similar to Oracle. It is generally widely used in OLTP applications. If no InnoDB configuration options are specified, MySQL creates an auto-extending data file named ibdata1 in the MySQL data directory, and two log files named ib_logfile0 and ib_logfile1.

When creating a table, you can specify the storage engine to be used by using the engine keyword. If omitted, the system default storage engine is used: CREATE TABLE t (i INT) ENGINE = MYISAM;

View the storage engine types supported by the system:

mysql> show engines;| Engine | Support | Comment || MyISAM | YES | Default engine as of MySQL 3.23 with greatperformance | 

 | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | 

 | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | 

 | BerkeleyDB | NO | Supports transactions and page-level locking | 

 | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | 

 | EXAMPLE | NO | Example storage engine | 

 | ARCHIVE | YES | Archive storage engine | 

 | CSV | NO | CSV storage engine | 

 | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables| 

 | FEDERATED | NO | Federated MySQL storage engine | 

 | MRG_MYISAM | YES | Collection of identical MyISAM tables | 

 | ISAM | NO | Obsolete storage engine | 12 rows in set (0.00 sec) 

The standard installation program only supports some engines. If you need to use other storage engines, you need to recompile the source code with different parameters. DEFAULT indicates the system's default storage engine, which can be changed by modifying the configuration parameters:

default-storage-engine=MyISAM

View the specific information of a storage engine

mysql> show engine InnoDB status\G;

Generally, the default installation system is INNODB

default-storage-engine=INNODB

1. You can add the --default-storage-engine or --default-table-type option to the command line when starting the database server.

2. A more flexible way is to specify the storage engine to be used when releasing the MySQL client that is provided with the MySQL server. The most direct way is to specify the type of storage engine when creating the table, as follows:

CREATE TABLE mytable (id int, titlechar(20)) ENGINE = INNODB

To change the storage engine of a table:

ALTER TABLE engineTest ENGINE = INNODB;

Modify the default storage engine:

In the MySQL configuration file (/etc/my.cnf in Linux), add default-storage-engine=INNODB after mysqld.

However, if the table is created with MyISAM, to change the storage engine of the entire database table, you generally have to modify each table one by one, which is rather cumbersome. You can export the database first, get the SQL, change MyISAM to INNODB, and then import it.

The above method of modifying the default storage engine in MySQL is all I have to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.