The role of indexes
With the appropriate index set, the database can greatly speed up the query speed by utilizing various fast positioning techniques. Especially when the table is large or the query involves multiple tables, using an index can speed up the query thousands of times; The IO cost of the database can be reduced, and the index can also reduce the sorting cost of the database. Ensure the uniqueness of the data table by creating a unique index; It accelerates the connection between tables. When using grouping and sorting, the grouping and sorting time can be greatly reduced.
1, enter the database, create the database, create tables
[root@master2 ~]# mysql -uroot -p ##Enter database
Enter password: ##Input password
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database school; ##Create a database school
Query OK, 1 row affected (0.00 sec)
mysql> use school; ##Use database
Database changed
mysql> create table info( ##Create Table
-> id int(4) not null primary key auto_increment, ##Set up primary key, increase automatically
-> name varchar(10) not null, ##Name type varchar is not empty
-> address varchar(50) default 'nanjing', ##Default Nanjing
-> age int(3) not null);
Query OK, 0 rows affected (0.02 sec)2, insert data into the table
mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22);
##insert data
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info; ##View Table Contents
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
mysql> desc info; ##View table structure
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| address | varchar(50) | YES | | nanjing | |
| age | int(3) | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)3, using create, alter, create table directly defines three ways to create an index (normal index, unique index)
mysql> create index index_age on info (age); ##Create Normal Index Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##View indexes in tables +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 1 | index_age | 1 | age | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> drop index index_age on info; ##Delete indexes from tables Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##View indexes in tables +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.01 sec) mysql> create unique index unique_name on info (name); ##Create Uniqueness Index Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##View indexes in tables +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | unique_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> drop index unique_name on info; ##Delete indexes from tables Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##View indexes in tables +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) mysql> alter table info add unique index index_name (name); ##Inserting table indexes using alter Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; ##View indexes in tables +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
4. Create two tables, associate them, and query multiple tables
mysql> create table user( ##Create user table
-> id int(4) not null primary key auto_increment, ##Set Primary Key and Auto Increase
-> name varchar(10) not null,
-> score decimal not null,
-> hobby int(2) not null default '1', ##Default 1
-> index index_score (score)); ##Set index score
Query OK, 0 rows affected (0.01 sec)
mysql> desc user; ##View table structure
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(10,0) | NO | MUL | NULL | |
| hobby | int(2) | NO | | 1 | |
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into user (name,score,hobby) values ('test01',88,1),('stu01',99,2),('wangwu',77,3);
##Insert data into a table
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from user; ##View Table Contents
+----+--------+-------+-------+
| id | name | score | hobby |
+----+--------+-------+-------+
| 1 | test01 | 88 | 1 |
| 2 | stu01 | 99 | 2 |
| 3 | wangwu | 77 | 3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)
mysql> create table hob( ##Create hob Table
-> id int(2) not null primary key,
-> hob_name varchar(10) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> desc hob; ##View table structure
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(2) | NO | PRI | NULL | |
| hob_name | varchar(10) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into hob (id,hob_name) values (1,'Read a Book'),(2,'motion'),(3,'Listen to the music'); ##Insert Table Data
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from hob; ##View Table Contents
+----+----------+
| id | hob_name |
+----+----------+
| 1 | Read a Book |
| 2 | motion |
| 3 | Listen to the music |
+----+----------+
3 rows in set (0.00 sec)
mysql> select * from user inner join hob on user.hobby=hob.id; ##Associate user and hob tables
+----+--------+-------+-------+----+----------+
| id | name | score | hobby | id | hob_name |
+----+--------+-------+-------+----+----------+
| 1 | test01 | 88 | 1 | 1 | Read a Book |
| 2 | stu01 | 99 | 2 | 2 | motion |
| 3 | wangwu | 77 | 3 | 3 | Listen to the music |
+----+--------+-------+-------+----+----------+
3 rows in set (0.00 sec)
mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id;
##Remove other content to display name and hob_name content
+--------+----------+
| name | hob_name |
+--------+----------+
| test01 | Read a Book |
| stu01 | motion |
| wangwu | Listen to the music |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; ##Set Simple Name
+--------+----------+
| name | hob_name |
+--------+----------+
| test01 | Read a Book |
| stu01 | motion |
| wangwu | Listen to the music |
+--------+----------+
3 rows in set (0.00 sec)
mysql> create view view_user as select u.name,h.hob_name from user u inner join hob h on u.hobby
##Create View
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view_user; ##view a chart
+--------+----------+
| name | hob_name |
+--------+----------+
| test01 | Read a Book |
| stu01 | motion |
| wangwu | Listen to the music |
+--------+----------+
3 rows in set (0.00 sec)
mysql> update user set hobby=3 where name='test01'; ##Modify the contents of the user table
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from view_user; ##Viewing a view is a link to a table
+--------+----------+
| name | hob_name |
+--------+----------+
| stu01 | motion |
| test01 | Listen to the music |
| wangwu | Listen to the music |
+--------+----------+
3 rows in set (0.00 sec)5, Full-text Index, Composite Index
mysql> select * from info; ##View Table Contents +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | +----+----------+----------+-----+ 2 rows in set (0.00 sec) mysql> show index from info; ##View Index of Table +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> create fulltext index full_addr on info (address); ##Create full-text index with address Query OK, 0 rows affected, 1 warning (0.07 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show index from info; ##View Table Index +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | | info | 1 | full_addr | 1 | address | NULL | 2 | NULL | NULL | YES | FULLTEXT | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) mysql> select * from user; ##View user table contents +----+--------+-------+-------+ | id | name | score | hobby | +----+--------+-------+-------+ | 1 | test01 | 88 | 3 | | 2 | stu01 | 99 | 2 | | 3 | wangwu | 77 | 3 | +----+--------+-------+-------+ 3 rows in set (0.00 sec) mysql> create index index_name_score on user (name,score); ##Create a combined index of name and scope Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from user; ##View Table Index +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_score | 1 | score | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_name_score | 1 | name | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_name_score | 2 | score | A | 3 | NULL | NULL | | BTREE | | | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
Transaction characteristics
Atomicity:
A transaction is a complete operation, and its elements are inseparable (atomic)
All elements in a transaction must be committed or rolled back as a whole
If any element in a transaction fails, the entire transaction fails
Uniformity:
When a transaction completes, the data must be in a consistent state: data stored in the database is in a consistent state before the transaction begins; data may be in an inconsistent state during an ongoing transaction; and data must return to a known consistent state again when the transaction completes successfully
Isolation:
All concurrent transactions that modify data are isolated from each other, indicating that the transaction must be independent and should not in any way depend on or influence other transactions
Transactions that modify data can access the data before another transaction that uses the same data starts, or after another transaction that uses the same data ends
Persistence:
Transaction persistence means that the results of a transaction are permanent regardless of whether the system fails or not
Once a transaction is committed, the effect of the transaction is permanently preserved in the database
1. Open the transaction and insert data into the table
mysql> select * from info; ##View table content data
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
mysql> insert into info (name,address,age) values ('wangwu','hangzhou',30)；##insert data
mysql> begin; ##Open Transaction
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info (name,address,age) values ('zhaoliu','hangzhou',31); ##insert data
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a; ##Set Save Node a
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info; ##View Table Data
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 4 | zhaoliu | hangzhou | 31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)
mysql> insert into info (name,address,age) values ('tianqi','hangzhou',32); ##Continue inserting data
Query OK, 1 row affected (0.00 sec)
mysql> savepoint b; ##Set Save Node b
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info (name,address,age) values ('heiba','hangzhou',32); ##Continue inserting data
Query OK, 1 row affected (0.00 sec)
mysql> select * from info; ##View Table Contents
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 4 | zhaoliu | hangzhou | 31 |
| 5 | tianqi | hangzhou | 32 |
| 6 | heiba | hangzhou | 32 |
+----+----------+----------+-----+
6 rows in set (0.00 sec)2, use another terminal to see if the insert was successful
[root@master2 ~]# mysql -uroot -p ##Enter database Enter password: ##Input password Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use school; ##Use database Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from info; ##Look at the table contents, but don't commit at this time +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | +----+----------+----------+-----+ 3 rows in set (0.00 sec)
3, use rollback to return the saved node
mysql> rollback to b; ##Using rollback to save node b Query OK, 0 rows affected (0.00 sec) mysql> select * from info; ##View Table Data +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 4 | zhaoliu | hangzhou | 31 | | 5 | tianqi | hangzhou | 32 | +----+----------+----------+-----+ 5 rows in set (0.00 sec) mysql> rollback to a; ##Rollback to save node a Query OK, 0 rows affected (0.00 sec) mysql> select * from info; ##View Table Data +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 4 | zhaoliu | hangzhou | 31 | +----+----------+----------+-----+ 4 rows in set (0.00 sec) mysql> rollback; ##Rollback to initial, exit transaction state Query OK, 0 rows affected (0.00 sec) mysql> select * from info; ##View Table Data +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | +----+----------+----------+-----+ 3 rows in set (0.00 sec)
4, commit the transaction using commit
mysql> begin; ##Open Transaction
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info (name,address,age) values ('heiba','hangzhou',32); ##insert data
Query OK, 1 row affected (0.00 sec)
mysql> commit; ##Submit Transaction
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info; ##View Table Data
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 7 | heiba | hangzhou | 32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)5, use another terminal to view
mysql> select * from info; ##View Table Data +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 7 | heiba | hangzhou | 32 | +----+----------+----------+-----+ 4 rows in set (0.00 sec)
6, another way of handling transactions
mysql> set autocommit=0; ##Set not to commit transactions automatically Query OK, 0 rows affected (0.00 sec) mysql> update info set address='beijing' where name='heiba'; ##Modify table data Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from info; ##View Table Information +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 7 | heiba | beijing | 32 | +----+----------+----------+-----+ 4 rows in set (0.00 sec) ##Another Terminal View mysql> select * from info; ##View table information without modification +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 7 | heiba | hangzhou | 32 | +----+----------+----------+-----+ 4 rows in set (0.00 sec) mysql> set autocommit=1; ##Open Autocommit Transaction Query OK, 0 rows affected (0.00 sec) ##Another Terminal View mysql> select * from info; ##Looking at the table data, you've modified it +----+----------+----------+-----+ | id | name | address | age | +----+----------+----------+-----+ | 1 | zhangsan | beijing | 20 | | 2 | lisi | shanghai | 22 | | 3 | wangwu | hangzhou | 30 | | 7 | heiba | beijing | 32 | +----+----------+----------+-----+ 4 rows in set (0.00 sec)
Storage Engines MyLSAM and InnoDB
1, view the system default storage engine
mysql> show engines; ##View the default storage engine innodb
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
mysql> show create table info; ##View the storage engine innodb for the created table
| info | CREATE TABLE "info" (
"id" int(4) NOT NULL AUTO_INCREMENT,
"name" varchar(10) NOT NULL,
"address" varchar(50) DEFAULT 'nanjing',
"age" int(3) NOT NULL,
PRIMARY KEY ("id"),
UNIQUE KEY "index_name" ("name"),
FULLTEXT KEY "full_addr" ("address")
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf82, Modify MySQL configuration file to set default storage engine
[root@localhost ~]# vim /etc/my.cnf ##Modify Profile [mysqld] user = mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 character_set_server=utf8 pid-file = /usr/local/mysql/mysql.pid socket = /usr/local/mysql/mysql.sock server-id = 1 default-storage-engine=Myisam ##Add a default storage engine to Myisam [root@master2 ~]# systemctl restart mysqld.service ##Restart MySQL Service
3, Enter the database
[root@master2 ~]# mysql -uroot -p ##Enter database
Enter password: ##Input password
mysql> use school; ##Use database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table a ( id int ); ##Create a table a
Query OK, 0 rows affected (0.00 sec)
mysql> show create table a; ##View table default storage engine Myisam
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------+
| a | CREATE TABLE "a" (
"id" int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table a engine=innodb; ##Modify table storage engine to innodb
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table a; ##View table storage engine innodb
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------+
| a | CREATE TABLE "a" (
"id" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)