Setting the engine MyISAM/InnoDB when creating a data table in MySQL

When I configured mysql, I set the default storage engine in the configuration file to InnoDB. Today I checked the difference between MyISAM and InnoDB. In the seventh article, "MyISAM supports GIS data, but InnoDB does not. That is, MyISAM supports the following spatial data objects: Point, Line, Polygon, Surface, etc."

As a student majoring in geographic information systems (actually surveying and mapping), a database that can store spatial data is a good database. Please forgive me for being a database novice.

There are three ways to configure the database engine:

(1) Modify the configuration file

Open the my.int configuration file in ~\MySQL\mysql-5.6.31-winx64 in the installation directory, and modify or add the following statement after [mysqld] (if you have not set it before):

default-storage-engine=INNODB

I will change it to MyISAM myself, and when you create a data table in the future, the default engine will be the current setting.

(2) Declare when creating a data table

mysql> create table test(
 -> id int(10) unsigned not null auto_increment,
 -> name varchar(10) character set utf8,
 -> age int(10),
 -> primary key(id)
 -> )
 -> engine=MyISAM
 -> ;

The above statements are for creating a table, as shown in the figure below. There is nothing to say.

Next, query the engine category of the created table.

show create table test;

(3) Change the engine of the data table

The engine type of a data table is not fixed. You can modify it through visual applications such as Navicat for MySQL, or through commands. Now change the engine of the newly created test table to InnoDB.

alter table test engine=innodb;

Additional knowledge: MySQL changes the data engine of all tables, MyISAM is set to InnoDB

1. Set up the SQL statement to execute first:

SELECT GROUP_CONCAT(CONCAT( 'ALTER TABLE ' ,TABLE_NAME ,' ENGINE=InnoDB; ') SEPARATOR '' ) 
FROM information_schema.TABLES AS t 
WHERE TABLE_SCHEMA = 'database' AND TABLE_TYPE = 'BASE TABLE';

Change the database to your database name. Here I assume my database is database

SELECT GROUP_CONCAT(CONCAT( 'ALTER TABLE ' ,TABLE_NAME ,' ENGINE=InnoDB; ') SEPARATOR '' ) 
FROM information_schema.TABLES AS t 
WHERE TABLE_SCHEMA = 'database' AND TABLE_TYPE = 'BASE TABLE';

2. Get a very long SQL, copy and execute it

3. View the field storage information of the current database

SHOW TABLE STATUS FROM database;

result:

The above article about setting the engine MyISAM/InnoDB when creating a data table in MySQL is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.