How to set MySQL database to use MyISAM by default?

To set the default storage engine, use the following syntax -

set @@default_storage_engine = ’yourEngineType’;

Now implement the above syntax to set the default engine to MyISAM. The query is as follows −

mysql> set @@default_storage_engine = 'MyISAM';
Query OK, 0 rows affected (0.05 sec)

Now you can check the default engine type with the help of SELECT statement. The query is as follows -

mysql> select @@default_storage_engine;

The following is the output showing the engine as MyISAM -

+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM                   |
+--------------------------+
1 row in set (0.00 sec)

Now create a table and check the default engine MyISAM.

Let's create a table. The query to create the table is as follows -

mysql> create table Followers
   -> (
   -> FollowerId int,
   -> FollowerName varchar(20)
   -> );
Query OK, 0 rows affected (0.32 sec)

Let us check the default engine type of the above table with the help of SHOW TABLE command. The query is as follows -

mysql> SHOW TABLE STATUS WHERE Name = 'Followers'\G

Output

Name: followers
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: 1
Create_time: 2019 - 02 - 12 00: 42: 27
Update_time: 2019 - 02 - 12 00: 42: 28
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set(0.00 sec)

In MySQL version 8.0.12, the default engine was InnoDB, but we changed it to MyISAM only for sessions. If you restart MySQL, the storage engine will be in the default MySQL mode, which is InnoDB. Let's restart MySQL. The query is as follows -

mysql> restart;
Query OK, 0 rows affected (0.20 sec)

Now check the default engine type again. Now InnoDB -

mysql> select @@default_storage_engine;
No connection. Trying to reconnect...
Connection id: 8
Current database: sample
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

The above is the detailed content of How to set MySQL database to use MyISAM by default?. For more information, please follow other related articles on the PHP Chinese website!