MySQL system variable table_type doesn't work?

The variable table_type doesn’t work since this variable is deprecated as of MySQL 5.5.3. Use default_storage_engine instead. Following is the syntax −

SET default_storage_engine = yourTableEngine;

The table engine name may be InnoDB or MyISAM. Here, we will set engine type to MyISAM −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> SET default_storage_engine</span><span class="pun">=</span><span class="typ">MyISAM</span><span class="pun">;</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.00</span><span class="pln"> sec</span><span class="pun">)</span>

Let us create a table.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">DemoTable</span>
<span class="pun">   (</span>
<span class="typ">   Id</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> NOT NULL AUTO_INCREMENT PRIMARY KEY</span>
<span class="pun">   );</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.40</span><span class="pln"> sec</span><span class="pun">)</span>

Now check the engine type of above table −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> SHOW TABLE STATUS WHERE </span><span class="typ">Name</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'DemoTable'</span><span class="pun">;</span>

This will produce the following output −

+--------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| DemoTable    | MyISAM | 10      | Fixed      | 0    | 0              | 0           | 1970324836974591 | 1024         | 0        | 1               | 2019-05-01 22:15:03 | 2019-05-01 22:15:03 | NULL | utf8_unicode_ci | NULL | | |
+--------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.34 sec)

Look at the above sample output, the engine type is MyISAM.

NOTE − In MySQL version 8.0.12, the default storage is InnoDB. Here we have changed the storage engine to MyISAM for current session only.