逍遥阁

mysql字段定义的常见写法

关键词: mysql

mysql字段定义的常见写法

### 数据类型

```
  BIT[(length)]
| 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[(fsp)]
| TIMESTAMP[(fsp)]
| DATETIME[(fsp)]
| YEAR
| CHAR[(length)] [BINARY]
  [CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length) [BINARY]
  [CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
  [CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
  [CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
  [CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
  [CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
  [CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
  [CHARACTER SET charset_name] [COLLATE collation_name]
| JSON
| spatial_type
```

### 字段定义

```
  数据类型 [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]
| 数据类型 [GENERATED ALWAYS] AS (expression)
      [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
      [NOT NULL | NULL] [[PRIMARY] KEY]
```

### 例子

```
create table test (
    `id` bigint(20) unsigned not null auto_increment primary key comment '自增字段',
    `name` varchar(16) character set utf8 collate utf8_general_ci null default 'test' comment '姓名'
)
```

admin
2017-07-25 17:06:50
mysql