MySQL 表选项

表选项–引擎

查看支持的引擎

 mysql> show engines\G*************************** 1. row ***************************      Engine: FEDERATED     Support: NO     Comment: Federated MySQL storage engineTransactions: NULL          XA: NULL  Savepoints: NULL*************************** 2. row ***************************      Engine: MRG_MYISAM     Support: YES     Comment: Collection of identical MyISAM tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 3. row ***************************      Engine: MyISAM     Support: DEFAULT     Comment: MyISAM storage engineTransactions: NO          XA: NO  Savepoints: NO*************************** 4. row ***************************      Engine: BLACKHOLE     Support: YES     Comment: /dev/null storage engine (anything you write to it disappears)Transactions: NO          XA: NO  Savepoints: NO*************************** 5. row ***************************      Engine: CSV     Support: YES     Comment: CSV storage engineTransactions: NO          XA: NO  Savepoints: NO*************************** 6. row ***************************      Engine: MEMORY     Support: YES     Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 7. row ***************************      Engine: ARCHIVE     Support: YES     Comment: Archive storage engineTransactions: NO          XA: NO  Savepoints: NO*************************** 8. row ***************************      Engine: InnoDB     Support: YES     Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES          XA: YES  Savepoints: YES*************************** 9. row ***************************      Engine: PERFORMANCE_SCHEMA     Support: YES     Comment: Performance SchemaTransactions: NO          XA: NO  Savepoints: NO9 rows in set (0.00 sec)

创建一个 SEXES 表，引擎为 MyISAM

CREATE TABLE SEXES(SEX CHAR(1) NOT NULL PRIMARY KEY)ENGINE = MYISAM

获得 PLAYERS, PENALTIES，SEXES 的引擎

mysql> SELECT TABLE_NAME, ENGINE    -> FROM INFORMATION_SCHEMA.TABLES    -> WHERE TABLE_NAME IN ('PLAYERS', 'PENALTIES', 'SEXES')\G*************************** 1. row ***************************TABLE_NAME: penalties    ENGINE: MyISAM*************************** 2. row ***************************TABLE_NAME: players    ENGINE: MyISAM*************************** 3. row ***************************TABLE_NAME: players    ENGINE: MyISAM*************************** 4. row ***************************TABLE_NAME: sexes    ENGINE: MyISAM4 rows in set (0.02 sec)

创建分表 PENALTIES_1990, PENALTIES_1991, and PENALTIES_1992，并使用 merge 引擎

CREATE TABLE PENALTIES_1990(    PAYMENTNO INTEGER NOT NULL PRIMARY KEY)ENGINE=MYISAM;INSERT INTO PENALTIES_1990 VALUES (1),(2),(3);CREATE TABLE PENALTIES_1991(    PAYMENTNO INTEGER NOT NULL PRIMARY KEY)ENGINE=MYISAM;INSERT INTO PENALTIES_1991 VALUES (4),(5),(6);CREATE TABLE PENALTIES_1992(    PAYMENTNO INTEGER NOT NULL PRIMARY KEY)ENGINE=MYISAM;INSERT INTO PENALTIES_1992 VALUES (7),(8),(9);CREATE TABLE PENALTIES_ALL(    PAYMENTNO INTEGER NOT NULL PRIMARY KEY)ENGINE = MERGE UNION = (PENALTIES_1990,PENALTIES_1991,PENALTIES_1992)  INSERT_METHOD = NO;mysql> SELECT * FROM PENALTIES_ALL;+-----------+| PAYMENTNO |+-----------+|         1 ||         2 ||         3 ||         4 ||         5 ||         6 ||         7 ||         8 ||         9 |+-----------+9 rows in set (0.00 sec)

表选项–AUTO_INCREMENT

CREATE TABLE CITY_NAMES(    SEQNO INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,    NAME VARCHAR(30) NOT NULL)AUTO_INCREMENT = 10;INSERT INTO CITY_NAMES VALUES (NULL, 'London');INSERT INTO CITY_NAMES VALUES (NULL, 'New York');INSERT INTO CITY_NAMES VALUES (NULL, 'Paris');mysql> SELECT * FROM CITY_NAMES;+-------+----------+| SEQNO | NAME     |+-------+----------+|    10 | London   ||    11 | New York ||    12 | Paris    |+-------+----------+3 rows in set (0.00 sec)

表选项–COMMENT

mysql> SELECT TABLE_NAME, TABLE_COMMENT    -> FROM INFORMATION_SCHEMA.TABLES    -> WHERE TABLE_NAME = 'PENALTIES'\G*************************** 1. row ***************************   TABLE_NAME: penaltiesTABLE_COMMENT:*************************** 2. row ***************************   TABLE_NAME: penaltiesTABLE_COMMENT: Penalties that have been paid by the tennis club2 rows in set (0.00 sec)

表选项–AVG_ROW_LENGTH, MAX_ROWS, MIN_ROWS
AVG_ROW_LENGTH 返回表中所有行的平均长度(字节单位)
MAX_ROWS 最大行数
MIN_ROWS 最小行数

当一个表变得比较大且实用MyISAM时指定这些选项可以防止MYSQL 突然指出表满了

创建表MATCHES，指定行数在100万到200万之间

CREATE TABLE MATCHES(    MATCHNO INTEGER NOT NULL PRIMARY KEY,    TEAMNO INTEGER NOT NULL,    PLAYERNO INTEGER NOT NULL,    WON SMALLINT NOT NULL,    LOST SMALLINT NOT NULL)AVG_ROW_LENGTH = 15    MAX_ROWS = 2000000  MIN_ROWS = 1000000

语法

<create table statement> ::=    CREATE [ TEMPORARY ] TABLE [ IF NOT EXISTS ]        <table specification> <table structure>[ <table option>... ]<table specification> ::= [ <database name> . ] <table name><table structure> ::= <table schema><table schema> ::=    ( <table element> [ , <table element> ]... )<table element> ::=    <column definition> |    <table integrity constraint> |    <index definition><column definition> ::=    <column name> <data type> [ <null specification> ]        [ <column integrity constraint> ]<null specification> ::= [ NOT ] NULL<column integrity constraint> ::=    PRIMARY KEY |    UNIQUE [ KEY ] |    <check integrity constraint><table integrity constraint> ::=    <primary key> |    <alternate key> |    <foreign key> |    <check integrity constraint><table option> ::=    ENGINE = <engine name> |    TYPE = <engine name> |    UNION = ( <table name> [ , <table name> ]... ) |    INSERT_METHOD = { NO | FIRST | LAST } |    AUTO_INCREMENT = <whole number> |    COMMENT = <alphanumeric literal> |    AVG_ROW_LENGTH = <whole number> |    MAX_ROWS = <whole number> |    MIN_ROWS = <whole number> |    [ DEFAULT ] CHARACTER SET { <name> | DEFAULT } |    [ DEFAULT ] COLLATE { <name> | DEFAULT } |    DATA DIRECTORY = <directory> |    INDEX DIRECTORY = <directory> |    CHECK_SUM = { 0 | 1 } |    DELAY_KEY_WRITE = { 0 | 1 } |    PACK_KEYS = { 0 | 1 | DEFAULT } |    PASSWORD = <alphanumeric literal> |    RAID_TYPE = { 1 | STRIPED | RAID0 } |    RAID_CHUNKS = <whole number> |    RAID_CHUNKSIZE = <whole number> |    ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED }