1、创建InnoDB表
mysql> SELECT @@default_storage_engine; //查看默认存储引擎
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
mysql> CREATE TABLE t1(a INT,
-> b CHAR(20))
-> ENGINE=InnoDB; //指定存储引擎,不指定为默认
Query OK, 0 rows affected (0.06 sec)
mysql> show table status from test where name='t1'\G; //查看表的属性
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2019-08-05 17:41:29
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
2、移动InnoDB表格
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student |
| t1 |
+----------------+
2 rows in set (0.00 sec)
mysql> rename table test.student to test2.s; //将表从一个数据库test移动到另一个数据库test2
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
mysql> use test2;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| s |
+-----------------+
3、更改表的存储引擎
mysql> show table status from test where name='t2'\G;
*************************** 1. row ***************************
Name: t2
Engine: MyISAM
mysql> alter table t2 engine=InnoDB; //更改t2的存储引擎为InnoDB
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status from test where name='t2'\G;
*************************** 1. row ***************************
Name: t2
Engine: InnoDB
4、InnoDB中AUTO_INCREMENT的处理
首先我们可以对Insert做一下简单的分类:
- 简单插入
可以预先确定要插入的行数