MySQL 表选项

表选项–引擎

查看支持的引擎

 mysql> show engines\G
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions: 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 engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
9 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: MyISAM
4 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: penalties
TABLE_COMMENT:
*************************** 2. row ***************************
   TABLE_NAME: penalties
TABLE_COMMENT: Penalties that have been paid by the tennis club
2 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 }