mysql create table

 

CREATE TABLE `mvc_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(100) NOT NULL,
`realname` varchar(20) NOT NULL,
`email` varchar(254) NOT NULL,
`face` varchar(100) NOT NULL,
`url` varchar(200) NOT NULL,
`about` longtext NOT NULL,
`addtime` datetime(6) NOT NULL,
`area_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `mvc_user_area_id_0ee8c234_fk_mvc_area_id` (`area_id`),
CONSTRAINT `mvc_user_area_id_0ee8c234_fk_mvc_area_id` FOREIGN KEY (`area_id`) REFERENCES `mvc_area` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

 

解释 PRIMARY KEY,  KEY,  CONSTRAINT:

1. 这里的PRIMARY KEY (`id`), 说明 字段 id是这个表的主键，主键的隐性说明就是唯一(unique)且不能为空(Not null)，一个表只能有一个主键，当然主键可以包含一个或多个字段, 当然一个表也可以不设置 主键

2.  KEY， 这里的key等同于index，所以这里也可以用 index，就是索引，这是为了和其他类型的数据库系统兼容， “KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.”

{INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... 

KEY `mvc_user_area_id_0ee8c234_fk_mvc_area_id` (`area_id`),  mvc_user_area_id_0ee8c234_fk_mvc_area_id 就是 index_name, area_id就是index_col_name, 也就是说在colume area_id上建立索引，index_type可以不指定

如果关键字属性PRIMARY KEY在列定义中指定，则PRIMARY KEY也可以只写为KEY， 比如：

`id` int(11) NOT NULL AUTO_INCREMENT KEY, 等同于 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY

 

比如：

CREATE TABLE `mvc_user22` (
`id` int(11) NOT NULL AUTO_INCREMENT KEY,
`username` varchar(20) NOT NULL,
`password` varchar(100) NOT NULL,
`realname` varchar(20) NOT NULL,
`email` varchar(254) NOT NULL,
`face` varchar(100) NOT NULL,
`url` varchar(200) NOT NULL,
`about` longtext NOT NULL,
`addtime` datetime(6) NOT NULL,
`area_id` int(11) NOT NULL,
KEY `mvc_user_area_id_0ee8c234_fk_mvc_area_id` (`area_id`),
CONSTRAINT `mvc_user22_area_id_0ee8c234_fk_mvc_area_id` FOREIGN KEY (`area_id`) REFERENCES `mvc_area` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

3. CONSTRAINT `mvc_user_area_id_0ee8c234_fk_mvc_area_id` FOREIGN KEY (`area_id`) REFERENCES `mvc_area` (`id`)， 指定一个 约束， 这里是一个外键约束， 这个外键是 表 mvc_area的主键 id, 所以插入数据的时候，会检查是否表mvc_area有这个id值，如果没有，则插入会出错。

 

注意： 同一数据库中不同表的约束(CONSTRAINT )不能重名？，不然创建表的时候会出错，会提示：

Can't write; duplicate key in table 'mvc_user22'

索引(index|key)可以重名。

 

根据官方文档:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options]

create_definition: col_name column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | CHECK (expr)

 

index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' 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 | SET DEFAULT

 

 

Indexes and Foreign Keys

  • CONSTRAINT symbol

    If the CONSTRAINT symbol clause is given, the symbol value, if used, must be unique in the database. A duplicate symbol results in an error. If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, a name for the constraint is created automatically.

  • PRIMARY KEY

    A unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.

    If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUEindex that has no NULL columns as the PRIMARY KEY.

    In InnoDB tables, keep the PRIMARY KEY short to minimize storage overhead for secondary indexes. Each secondary index entry contains a copy of the primary key columns for the corresponding row. (See Section 14.8.2.1, “Clustered and Secondary Indexes”.)

    In the created table, a PRIMARY KEY is placed first, followed by all UNIQUE indexes, and then the nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicated UNIQUE keys.

    A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.

    If a PRIMARY KEY consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECTstatements.

    In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you do not assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See Section 13.7.5.22, “SHOW INDEX Syntax”.

  • KEY | INDEX

    KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

  • UNIQUE

    A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

    If a UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECTstatements.

  • FULLTEXT

    A FULLTEXT index is a special type of index used for full-text searches. Only the InnoDB and MyISAM storage engines supportFULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See Section 12.9, “Full-Text Search Functions”, for details of operation. A WITH PARSER clause can be specified as an index_option value to associate a parser plugin with the index if full-text indexing and searching operations need special handling. This clause is valid only for FULLTEXTindexes. Both InnoDB and MyISAM support full-text parser plugins. See Full-Text Parser Plugins and Section 28.2.4.4, “Writing Full-Text Parser Plugins” for more information.

  • SPATIAL

    You can create SPATIAL indexes on spatial data types. Spatial types are supported only for MyISAM and InnoDB tables, and indexed columns must be declared as NOT NULL. See Section 11.5, “Spatial Data Types”.

  • FOREIGN KEY

    MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. For definition and option information, see reference_definition, andreference_option.

    Partitioned tables employing the InnoDB storage engine do not support foreign keys. See Section 22.6, “Restrictions and Limitations on Partitioning”, for more information.

  • CHECK

    The CHECK clause is parsed but ignored by all storage engines. See Section 1.8.2.3, “Foreign Key Differences”.

  • index_col_name

    • An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

    • Prefixes, defined by the length attribute, can be up to 767 bytes long for InnoDB tables or 3072 bytes if theinnodb_large_prefix option is enabled. For MyISAM tables, the prefix limit is 1000 bytes.

      Prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE, ALTER TABLE, and CREATE INDEXstatements is interpreted as number of characters for nonbinary string types (CHAR, VARCHAR, TEXT) and number of bytes for binary string types (BINARY, VARBINARY, BLOB). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.

  • index_type

    Some storage engines permit you to specify an index type when creating an index. The syntax for the index_type specifier isUSING type_name.

    Example:

    CREATE TABLE lookup
  (id INT, INDEX USING BTREE (id))
  ENGINE = MEMORY;

    The preferred position for USING is after the index column list. It can be given before the column list, but support for use of the option in that position is deprecated and will be removed in a future MySQL release.

  • index_option

    index_option values specify additional options for an index.

    • KEY_BLOCK_SIZE

      For MyISAM tables, KEY_BLOCK_SIZE optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. A KEY_BLOCK_SIZE value specified for an individual index definition overrides the table-level KEY_BLOCK_SIZE value.

      For information about the table-level KEY_BLOCK_SIZE attribute, see Table Options.

    • WITH PARSER

      The WITH PARSER option can only be used with FULLTEXT indexes. It associates a parser plugin with the index if full-text indexing and searching operations need special handling. Both InnoDB and MyISAM support full-text parser plugins. If you have a MyISAM table with an associated full-text parser plugin, you can convert the table to InnoDB using ALTER TABLE.

    • COMMENT

      In MySQL 5.7, index definitions can include an optional comment of up to 1024 characters.

      You can set the InnoDB MERGE_THRESHOLD value for an individual index using the index_option COMMENT clause. SeeSection 14.6.13, “Configuring the Merge Threshold for Index Pages”.

    For more information about permissible index_option values, see Section 13.1.14, “CREATE INDEX Syntax”. For more information about indexes, see Section 8.3.1, “How MySQL Uses Indexes”.

  • reference_definition

    For reference_definition syntax details and examples, see Section 13.1.18.6, “Using FOREIGN KEY Constraints”. For information specific to foreign keys in InnoDB, see Section 14.8.1.6, “InnoDB and FOREIGN KEY Constraints”.

    InnoDB and NDB tables support checking of foreign key constraints. The columns of the referenced table must always be explicitly named. Both ON DELETE and ON UPDATE actions on foreign keys are supported. For more detailed information and examples, see Section 13.1.18.6, “Using FOREIGN KEY Constraints”. For information specific to foreign keys in InnoDB, see Section 14.8.1.6, “InnoDB and FOREIGN KEY Constraints”.

    For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLEstatements. See Section 1.8.2.3, “Foreign Key Differences”.

    Important

    For users familiar with the ANSI/ISO SQL Standard, please note that no storage engine, including InnoDB, recognizes or enforces the MATCH clause used in referential integrity constraint definitions. Use of an explicitMATCH clause will not have the specified effect, and also causes ON DELETE and ON UPDATE clauses to be ignored. For these reasons, specifying MATCH should be avoided.

    The MATCH clause in the SQL standard controls how NULL values in a composite (multiple-column) foreign key are handled when comparing to a primary key. InnoDB essentially implements the semantics defined by MATCH SIMPLE, which permit a foreign key to be all or partially NULL. In that case, the (child table) row containing such a foreign key is permitted to be inserted, and does not match any row in the referenced (parent) table. It is possible to implement other semantics using triggers.

    Additionally, MySQL requires that the referenced columns be indexed for performance. However, InnoDBdoes not enforce any requirement that the referenced columns be declared UNIQUE or NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only keys that are both UNIQUE (or PRIMARY) and NOT NULL.

    MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

  • reference_option

    For information about the RESTRICT, CASCADE, SET NULL, NO ACTION, and SET DEFAULT options, see Section 13.1.18.6, “Using FOREIGN KEY Constraints”.

posted @ 2017-12-25 21:26  alxe_yu  阅读(68)  评论(0)    收藏  举报