一、简述
mysql> show engines;
+
| Engine | Support | Comment |
+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| binlog | YES | This is a meta storage engine to represent the binlog in a transaction |
| ISAM | NO | Obsolete storage engine |
+
13 rows in set (0.00 sec)
数据库5.0.15默认存储引擎是MyISAM，所以本文围绕MyISAM存储建表。
数据库将创建 .frm 文件表示表的结构。
MyISAM存储引擎每张表分为两个文件：
二、语法
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] TBL_NAME
[(CREATE_DEFINITION,...)]
[TABLE_OPTIONS] [SELECT_STATEMENT]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] TBL_NAME
[(] LIKE OLD_TBL_NAME [)];
CREATE_DEFINITION:
COLUMN_DEFINITION
| [CONSTRAINT [SYMBOL]] PRIMARY KEY [INDEX_TYPE] (INDEX_COL_NAME,...)
| KEY [INDEX_NAME] [INDEX_TYPE] (INDEX_COL_NAME,...)
| INDEX [INDEX_NAME] [INDEX_TYPE] (INDEX_COL_NAME,...)
| [CONSTRAINT [SYMBOL]] UNIQUE [INDEX]
[INDEX_NAME] [INDEX_TYPE] (INDEX_COL_NAME,...)
| [FULLTEXT|SPATIAL] [INDEX] [INDEX_NAME] (INDEX_COL_NAME,...)
| [CONSTRAINT [SYMBOL]] FOREIGN KEY
[INDEX_NAME] (INDEX_COL_NAME,...) [REFERENCE_DEFINITION]
| CHECK (EXPR)
COLUMN_DEFINITION:
COL_NAME TYPE [NOT NULL | NULL] [DEFAULT DEFAULT_VALUE]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'STRING'] [REFERENCE_DEFINITION]
TYPE:
TINYINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| INT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| INTEGER[(LENGTH)] [UNSIGNED] [ZEROFILL]
| BIGINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| REAL[(LENGTH,DECIMALS)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(LENGTH,DECIMALS)] [UNSIGNED] [ZEROFILL]
| FLOAT[(LENGTH,DECIMALS)] [UNSIGNED] [ZEROFILL]
| DECIMAL(LENGTH,DECIMALS) [UNSIGNED] [ZEROFILL]
| NUMERIC(LENGTH,DECIMALS) [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| CHAR(LENGTH) [BINARY | ASCII | UNICODE]
| VARCHAR(LENGTH) [BINARY]
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
| TEXT [BINARY]
| MEDIUMTEXT [BINARY]
| LONGTEXT [BINARY]
| ENUM(VALUE1,VALUE2,VALUE3,...)
| SET(VALUE1,VALUE2,VALUE3,...)
| SPATIAL_TYPE
INDEX_COL_NAME:
COL_NAME [(LENGTH)] [ASC | DESC]
REFERENCE_DEFINITION:
REFERENCES TBL_NAME [(INDEX_COL_NAME,...)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE REFERENCE_OPTION]
[ON UPDATE REFERENCE_OPTION]
REFERENCE_OPTION:
RESTRICT | CASCADE | SET NULL | NO ACTION
TABLE_OPTIONS: TABLE_OPTION [TABLE_OPTION] ...
TABLE_OPTION:
{ENGINE|TYPE} = ENGINE_NAME
| AUTO_INCREMENT = VALUE
| AVG_ROW_LENGTH = VALUE
| [DEFAULT] CHARACTER SET CHARSET_NAME [COLLATE COLLATION_NAME]
| CHECKSUM = {0 | 1}
| COMMENT = 'STRING'
| CONNECTION = 'CONNECT_STRING'
| MAX_ROWS = VALUE
| MIN_ROWS = VALUE
| PACK_KEYS = {0 | 1 | DEFAULT}
| PASSWORD = 'STRING'
| DELAY_KEY_WRITE = {0 | 1}
| ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| RAID_TYPE = { 1 | STRIPED | RAID0 }
RAID_CHUNKS = VALUE
RAID_CHUNKSIZE = VALUE
| UNION = (TBL_NAME[,TBL_NAME]...)
| INSERT_METHOD = { NO | FIRST | LAST }
| DATA DIRECTORY = 'ABSOLUTE PATH TO DIRECTORY'
| INDEX DIRECTORY = 'ABSOLUTE PATH TO DIRECTORY'
SELECT_STATEMENT:
[IGNORE | REPLACE] [AS] SELECT ... (SOME LEGAL SELECT STATEMENT)
三、简单操作
3.1 创建表
- 首先当前用户有创建表的权限
- 创建的表在当前数据库中
- 如果重复创建，将报错
3.1.1 直接创建
mysql> create table t1(name varchar(30), age int);
Query OK, 0 rows affected (0.01 sec)
可以看到数据库目录下多了三个文件
$ cd /usr/local/mysql5.0.15/var/test
$ ls t1*
t1.MYD t1.MYI t1.frm
重复创建,不成功
mysql> create table t1(version varchar(25));
ERROR 1050 (42S01): Table 't1' already exists
为了防止报错，可以增加判断条件
mysql> create table if not exists t1(version varchar(25));
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` varchar(30) default NULL,
`age` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3.1.2 使用like创建表
> create table t2 like t1;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t2 \G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`name` varchar(30) default NULL,
`age` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
能看到t2和t1的结构完全相同
3.1.3 使用select创建表
先在t1表中插入一些数据
mysql>insert into t1 (name, age) values ('zhangsan', 10),('lisi',20), ('wangwu', 18);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table t3 select name from t1;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t3 \G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`name` varchar(30) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from t3;
+
| name |
+
| zhangsan |
| lisi |
| wangwu |
+
3 rows in set (0.00 sec)
能看出，通过select创建表，表结构就是select选择的列，并且将选择的数据同时也插入到新表中
当使用if not exists时，即使表已经存在，数据也会插入
前提时存在的表结构和select的列对应
mysql> select * from t3;
Empty set (0.00 sec)
mysql> create table if not exists t3 select * from t1;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> create table if not exists t3 select name from t1;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+
| name |
+
| zhangsan |
| lisi |
| wangwu |
+
3 rows in set (0.00 sec)
3.1.4 使用dbname.tablename创建表
- 默认创建的表都在当前数据库中
- MySQL5.0开始可以通过DB_NAME.TBL_NAME在特定的数据库下创建表
mysql> show databases;
+
| Database |
+
| information_schema |
| mydb |
| mysql |
| test |
+
4 rows in set (0.00 sec)
mysql> use mydb
Database changed
mysql> show tables;
+
| Tables_in_mydb |
+
| test |
+
1 row in set (0.00 sec)
mysql> use test
Database changed
mysql> show tables;
+
| Tables_in_test |
+
| t1 |
| t2 |
| t3 |
| test |
+
4 rows in set (0.00 sec)
mysql> create table mydb.t4(id int, name varchar(24), age int);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+
| Tables_in_test |
+
| t1 |
| t2 |
| t3 |
| test |
+
4 rows in set (0.00 sec)
mysql> use mydb
Database changed
mysql> show tables;
+
| Tables_in_mydb |
+
| t4 |
| test |
+
2 rows in set (0.00 sec)
mysql> show create table t4 \G
*************************** 1. row ***************************
Table: t4
Create Table: CREATE TABLE `t4` (
`id` int(11) default NULL,
`name` varchar(24) default NULL,
`age` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3.2 修改表结构
3.2.1 增加列
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` varchar(30) default NULL,
`age` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table t1 add column id int;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` varchar(30) default NULL,
`age` int(11) default NULL,
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3.2.2 删除列
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` varchar(30) default NULL,
`age` int(11) default NULL,
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table t1 drop column age;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` varchar(30) default NULL,
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3.2.3 修改列类型
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` varchar(30) default NULL,
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table t1 modify column name int;
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` int(11) default NULL,
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3.2.4 修改字符集
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` int(11) default NULL,
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table t1 change name name char(30) character set utf8;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` char(30) character set utf8 default NULL,
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
3.2.5 修改表引擎
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` char(30) character set utf8 default NULL,
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table t1 engine=innodb;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` char(30) character set utf8 default NULL,
`id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql>
3.2 修改表数据
mysql> select * from t1;
+
| name | id |
+
| 0 | NULL |
| 0 | NULL |
| 0 | NULL |
+
3 rows in set (0.00 sec)
mysql> update t1 set name = 'zhangsan';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from t1;
+
| name | id |
+
| zhangsan | NULL |
| zhangsan | NULL |
| zhangsan | NULL |
+
3 rows in set (0.00 sec)
3.3 清空表数据
mysql> truncate table t1;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from t1;
Empty set (0.00 sec)
或者使用delete
mysql> insert into t1 values ('lisi', 3),('wangwu', 5), ('zhangsan', 8);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+
| name | id |
+
| lisi | 3 |
| wangwu | 5 |
| zhangsan | 8 |
+
3 rows in set (0.00 sec)
mysql> delete from t1;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from t1;
Empty set (0.00 sec)
3.4 删除表
mysql> show tables;
+
| Tables_in_test |
+
| t1 |
| t2 |
| t3 |
| test |
+
4 rows in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+
| Tables_in_test |
+
| t2 |
| t3 |
| test |
+
3 rows in set (0.00 sec)
四、实现
client请求创建表
mysql> create table t(id int, name varchar(20), age int);
server接收client的请求，进行解析，解析后发现是创建表，如下为调用栈
fields=..., keys=..., internal_tmp_table=false, select_field_count=0) at sql_table.cc:1494
at sql_parse.cc:5536
packet=0x9fc37c1 "create table t(id int, name varchar(20), age int)", packet_length=50) at sql_parse.cc:1697
关键函数为mysql_create_table
bool mysql_create_table(THD *thd,const char *db, const char *table_name,
HA_CREATE_INFO *create_info,
List<create_field> &fields,
List<Key> &keys,bool internal_tmp_table,
uint select_field_count)
{
...
if (check_engine(thd, table_name, &create_info->db_type))
DBUG_RETURN(TRUE);
...
alias= table_case_name(create_info, table_name);
file=get_new_handler((TABLE*) 0, create_info->db_type);
...
if (!create_info->default_table_charset)
{
...
build_table_path(path, sizeof(path), db, MY_DB_OPT_FILE, "");
load_db_opt(thd, path, &db_info);
create_info->default_table_charset= db_info.default_table_charset;
}
...
if (mysql_prepare_table(thd, create_info, &fields,
&keys, internal_tmp_table, &db_options, file,
&key_info_buffer, &key_count,
select_field_count))
DBUG_RETURN(TRUE);
...
build_table_path(path, sizeof(path), db, alias, reg_ext);
...
if (!access(path,F_OK))
{
if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
goto warn;
my_error(ER_TABLE_EXISTS_ERROR,MYF(0),table_name);
goto end;
}
}
...
if (rea_create_table(thd, path, db, table_name,
create_info, fields, key_count,
key_info_buffer))
goto end;
...
if (!internal_tmp_table && mysql_bin_log.is_open())
{
thd->clear_error();
Query_log_event qinfo(thd, thd->query, thd->query_length, FALSE, FALSE);
mysql_bin_log.write(&qinfo);
}
error= FALSE;
end:
...
thd->proc_info="After create";
DBUG_RETURN(error);
warn:
error= FALSE;
...
create_info->table_existed= 1;
goto end;
}
这里主要关注函数rea_create_table
Thread 11 "mysqld" hit Breakpoint 2, create_frm (thd=0x9f8e870, name=0x7ffffc3fdda0 "./test/t.frm", db=0x7ffff001b600 "test",
table=0x7ffff0002d38 "t", reclength=30, fileinfo=0x7ffffc3fd5a0 "", create_info=0x9f8f240, keys=0) at table.cc:1396
1396 {
(gdb) bt
fileinfo=0x7ffffc3fd5a0 "", create_info=0x9f8f240, keys=0) at table.cc:1396
table=0x7ffff0002d38 "t", create_info=0x9f8f240, create_fields=..., keys=0, key_info=0x7ffff0003360, db_file=0x7ffff0003360)
at unireg.cc:127
db=db@entry=0x7ffff001b600 "test", table=table@entry=0x7ffff0002d38 "t", create_info=create_info@entry=0x9f8f240,
create_fields=..., keys=0, key_info=0x7ffff0003360) at unireg.cc:261
create_info=create_info@entry=0x9f8f240, fields=..., keys=..., internal_tmp_table=false, select_field_count=0)
at sql_table.cc:1618
packet=packet@entry=0x9fc37c1 "create table t(id int, name varchar(20), age int)", packet_length=packet_length@entry=50)
at sql_parse.cc:1697
|