**目录** * [1 建立表格](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#1__3) * [1.1 表格属性](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#11__25) * [1.2 字串字段属性](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#12__63) * [1.3 数值字段属性](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#13__73) * [1.4 通用字段属性](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#14__81) * [1.5 TIMESTAMP字段型态与默认值](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#15_TIMESTAMP_119) * [1.6 使用其它表格建立一个新表格](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#16__157) * [1.7 建立暂存表格](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#17__198) * [2 修改表格](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#2__211) * [2.1 增加字段](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#21__217) * [2.2 修改字段](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#22__241) * [2.3 删除字段](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#23__255) * [2.4 修改表格名称](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#24__265) * [3 删除表格](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#3__275) * [4 索引介绍](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#4__283) * [5 建立索引](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#5__313) * [5.1 在建立表格的时候建立索引](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#51__317) * [5.2 在修改表格的时候建立索引](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#52__357) * [5.3 使用“CREATE INDEX”建立索引](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#53_CREATE_INDEX_367) * [6 索引的名称](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#6__385) * [7 删除索引](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#7__399) * [8 数值字段型态与AUTO\_INCREMENT](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#8_AUTO_INCREMENT_417) * [9 查询表格与索引资讯](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#9__481) * [9.1 表格相关资讯](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#91__485) * [9.2 索引相关资讯](https://www.kancloud.cn/thinkphp/mysql-tutorial/36443#92__522) # 1 建立表格 在建立好数据库以后，就可以根据储存资料的需求，使用SQL叙述建立所有需要的表格(table)。建立表格的设定非常多，以建立“world.city”表格来说，它的叙述会像这样： [](http://box.kancloud.cn/2015-09-15_55f7e96e360eb.png) 根据不同的需求，建立表格的语法有好几种，下列是建立表格基本的语法： [](http://box.kancloud.cn/2015-09-15_55f7e96f0dfb2.png) MySQL规定一个表格中至少要有一个字段，在设定表格中的字段时，至少要明确的决定字段的名称与型态，其它的字段设定都是选择性的，如果有一个以上字段，要使用逗号隔开： [](http://box.kancloud.cn/2015-09-15_55f7e96f50f47.png) 使用需要的资料型态，就可以建立一个可以储存亲友通讯录的表格： [](http://box.kancloud.cn/2015-09-15_55f7e975143ff.png) 建立表格的时候可以使用“IF NOT EXISTS”选项，预防发生表格已存在的错误： [](http://box.kancloud.cn/2015-09-15_55f7e97619ce9.png) ## 1.1 表格属性 建立表格的时候也可以为表格加入需要的表格属性(table attributes)设定，这里会先讨论关于储存引擎、字符集和collation的属性设定。如果你在建立表格的时侯，没有指定这些属性，MySQL会使用服务器默认的储存引擎作为表格的储存引擎，字符集与collation会使用数据库默认的设定。 你可以针对表格的需求，设定它使用的储存引擎、字符集与collation： [](http://box.kancloud.cn/2015-09-15_55f7e97696d08.png) 下列的叙述在建立“addressbook”表格的时候，使用“ENGINE”、“CHARCTER SET”和“COLLATE”设定表格自己使用的储存引擎、字符集与collation： [](http://box.kancloud.cn/2015-09-15_55f7e976dfaf5.png) 注：根据语法的说明，“CHARCTER SET”也可以使用比较简短的“CHARSET”；另外在设定时都可以省略“=”。 MySQL数据库服务器支援许多不同应用的储存引擎，你可以使用“SHOW ENGINES”查询： [](http://box.kancloud.cn/2015-09-15_55f7e97769f6a.png) 在建立表格的时候，如果没有使用“ENGINE”设定储存引擎，那就会使用MySQL数据库服务器默认的储存引擎。你可以使用下列的方式修改MySQL数据库服务器默认的储存引擎设定： * 修改设定档：MySQL数据库服务器在启动时会读取一个名称为“my.ini”的设定档，档案中有许多启动数据库服务器时需要的资讯。其中就包含默认的储存引擎设定，你可以修改这个设定后再重新启动数据库服务器，让新的设定生效： [](http://box.kancloud.cn/2015-09-15_55f7e9782a5ca.png) * 设定储存引擎：你也可以使用“SET”叙述设定默认的储存引擎： [](http://box.kancloud.cn/2015-09-15_55f7e97854eab.png) 在建立表格时指定字符集与collation会有一些不同的组合。如果只有指定字符集，MySQL会使用你指定字符集的默认collation： [](http://box.kancloud.cn/2015-09-15_55f7e98d6cb22.png) 如果只有使用“COLLATE”指定collation，MySQL会使用你指定collation所属的字符集： [](http://box.kancloud.cn/2015-09-15_55f7e997cd707.png) 注：建立表格的时候，不管你有没有指定，表格都会有字符集与collation的设定。在这个表格中的“非二进位制、non-binary”字串型态字段，还有“ENUM”与“SET”型态字段，都会使用表格默认的字符集与collation。 ## 1.2 字串字段属性 如果一个字段的型态是字串的话，你还可以依照需求加入字串型态的字段属性(column attributes)。“非二进位制、non-binary”字串可以额外设定字符集与collation： [](http://box.kancloud.cn/2015-09-15_55f7e99865dcb.png) 每一个表格都会有一个默认的字符集与collation设定，如果没有指定字段的字符集与collation，就会使用默认的设定： [](http://box.kancloud.cn/2015-09-15_55f7e998a970e.png) ## 1.3 数值字段属性 数值型态字段专用的属性设定有“UNSIGNED”、“ZEROFILL”与“AUTO\_INCREMENT”： [](http://box.kancloud.cn/2015-09-15_55f7e998f236f.png) 注：数值型态字段设定为“UNSIGNED”与“ZEROFILL”的效果在“第八章、表格与索引、建立表格、数值字段属性”中已经讨论过；而“AUTO\_INCREMENT”的设定与索引有关，所以在这一章后面索引的部份一起讨论。 ## 1.4 通用字段属性 除了字串与数值两种字段专用的字段属性设定外，还有许多可以用在所有型态的字段属性： [](http://box.kancloud.cn/2015-09-15_55f7e999d8a69.png) “NOT NULL”字段属性可以用来禁止某个字段储存“NULL”值，一般来说，“NULL”值用来表示一个字段的资料是“不确定”、“未知”或“没有”。不过有一些字段并不能出现“NULL”值，不然就会成为一笔很奇怪的纪录了： [](http://box.kancloud.cn/2015-09-15_55f7e99b09fa4.png) 使用“NULL”或“NOT NULL”设定字段属性后，在查询表格字段资讯时，是在“Null”字段用“YES”或“NO”来表示： [](http://box.kancloud.cn/2015-09-15_55f7e9a5620f5.png) 如果一个表格中，有设定为“NOT NULL”的字段，那就要注意你在新增或修改纪录时指定的资料，不能够违反这些规则： [](http://box.kancloud.cn/2015-09-15_55f7e9a7717fd.png) 使用“DEFAULT”关键字可以设定字段的默认值，你可以自己指定任何想要的默认值，在新增或修改资料的时候都有可能会使用到字段的默认值。要特别注意的是MySQL限制你的默认值只能是“一个明确的值”，也就是默认值的设定不可以使用任何函式或运算式。 如果你没有为字段使用“DEFAULT”关键字设定默认值，而且也没有设定为“NOT NULL”，MySQL会自动为你加入默认值的设定： [](http://box.kancloud.cn/2015-09-15_55f7e9a977758.png) 以通讯录表格来说，如果纪录的地址大部份都是“Taipei”的话，你可以为“address”字段设定一个默认值： [](http://box.kancloud.cn/2015-09-15_55f7e9a9d5111.png) 使用“DEFAULT”关键字加入默认值的设定以后，就可以在新增或修改资料的时候使用： 默认值的设定要注意下列的规则： * “BLOB”与“TEXT”字段型态不可以使用\[DEFAULT\]关键字指定默认值，其它的字段型态都可以 * 不能与其它的字段设定造成冲突。例如一个设定为“NOT NULL”的字段，却使用“DEFAULT NULL”设定默认值为“NULL” * 指定的默认值要符合字段型态。例如“DATE”型态字段使用“DEFAULT ‘Hello!’”指定默认值 注：“UNIQUE KEY”与“PRIMARY KEY”在这一章后面索引的部份一起讨论。 ## 1.5 TIMESTAMP字段型态与默认值 “TIMESTAMP”字段是日期时间资料的一种，它除了具有“时区、timezone”的特性外，也可以搭配“DEFAULT”和“ON UPDATE”来完成一些比较特殊的需求： [](http://box.kancloud.cn/2015-09-15_55f7e9aaa6eea.png) 在表格中使用“TIMESTAMP”型态的字段时，如果你没有设定它们的字段属性，MySQL会自动帮你在第一个“TIMESTAMP”字段加入“NOT NULL”、“DEFAULT”和“ON UPDATE”三个字段属性的设定。 * “NOT NULL”不允许你储存“NULL”值 * “DEFAULT CURRENT\_TIMESTAMP”设定默认值为目前的日期时间。在所有字段型态中，只有“TIMESTAMP”可以使用“CURRENT\_TIMESTAMP”指定默认值；其它的字段型态，在指定默认值只能是“一个明确的值” * “ON UPDATE”可以指定在修改纪录的时候，MySQL自动帮你填入的资料 其它没有设定字段属性的“TIMESTAMP”字段，MySQL会帮你加入“NOT NULL”与“DEFAULT”两个字段属性。 “DEFAULT CURRENT\_TIMESTAMP”字段属性的效果，在你新增纪录的时候就可以看得出来了： [](http://box.kancloud.cn/2015-09-15_55f7e9ab1fe9f.png) 而“ON UPDATE CURRENT\_TIMESTAMP”字段属性，会在修改纪录的时候产生效果： [](http://box.kancloud.cn/2015-09-15_55f7e9abeae78.png) “TIMESTAMP”字段型态很适合用来记录资料新增或修改的日期与时间。可是如果在同一笔纪录中，要使用一个字段记录新增资料的日期与时间，而使用另一个字段记录修改资料的日期与时间。为了应付这样的需求，你应该会使用下列的字段定义： [](http://box.kancloud.cn/2015-09-15_55f7e9acd19e8.png) 在一个表格中，MySQL限制“CURRENT\_TIMESTAMP”只能在一个字段出现，所以当有这样的需求出现时，你必须使用MySQL提供给你的特殊设定方式来解决： [](http://box.kancloud.cn/2015-09-15_55f7e9ae46448.png) 建立好这样的表格以后，看起来虽然怪怪的，不过当你指定“created”字段的值为“NULL”的时候，MySQL会自动为你填入目前的日期与时间： [](http://box.kancloud.cn/2015-09-15_55f7e9aea8acf.png) 后续在修改资料的时候，就只会在“updated”字段填入目前的日期与时间： [](http://box.kancloud.cn/2015-09-15_55f7e9af56271.png) ## 1.6 使用其它表格建立一个新表格 在数据库中建立需要的表格，通常是使用上列讨论的方式，根据自己的需求，建立一个新的表格来储存需要保存的资料；在一些比较特别的情况，你可能会使用一个现有的表格来建立新的表格，这样的需求可以使用下列的语法： [](http://box.kancloud.cn/2015-09-15_55f7e9afe74b6.png) 以“world”数据库中的“city”来说，下列的查询叙述可以传回台湾的城市与人口数： [](http://box.kancloud.cn/2015-09-15_55f7e9b07a901.png) 如果你想要建立一个新表格，这个表格中的资料就是上列查询的结果，就可以使用这种建立表格的语法： [](http://box.kancloud.cn/2015-09-15_55f7e9b103012.png) 使用这种语法建立的新表格，可以省略字段定义的工作，新表格会使用原有表格的字段名称与定义，而且在查询叙述中传回的资料，会直接新增到新建立的表格中： [](http://box.kancloud.cn/2015-09-15_55f7e9b2f2a9f.png) 你也可以在建立新表格的时候，使用字段定义来设定新表格的字段型态与其它属性： [](http://box.kancloud.cn/2015-09-15_55f7e9bda4e7a.png) 如果需要的话，也可以加入查询叙述中没有的字段： [](http://box.kancloud.cn/2015-09-15_55f7e9be389b4.png) 使用这种语法建立表格时有下列几个重点： * MySQL使用查询结果的字段名称与型态来建立新的表格 * 如果没有指定储存引擎、字符集或collation的话，建立的新表格使用数据库默认的储存引擎、字符集与collation * 查询表格中，字段的索引与“AUTO\_INCREMENT”设定都会被忽略 如果只需要借用一个已经存在的表格字段定义，可是并不需要纪录资料的话，你可以使用下列的语法来建立新表格： [](http://box.kancloud.cn/2015-09-15_55f7e9beb1a3b.png) 使用这种语法建立的新表格，并不会新增纪录到新表格中，可是包含索引与“AUTO\_INCREMENT”设定都会套用在新表格，除了下列两个例外： * 使用“MyISAM”储存引擎时，你可以在建立表格的时候使用“DATA DIRECTORY”与“INDEX DIRECTORY”指定资料与索引档案的资料夹位置；建立的新表格会忽略这些设定，而使用数据库默认的资料夹 * 字段的“FOREIGN KEY”与表格的“REFERENCES”属性设定都会被忽略 ## 1.7 建立暂存表格 上列讨论的建立表格方式，都可以在建立表格的时候，依照需要加入“TEMPORARY”关键字，指定这个新建立的表格为“用户端暂时存在”的表格： [](http://box.kancloud.cn/2015-09-15_55f7e9bf3fbbd.png) “TEMPORARY”表格有下列重点： * “TEMPORARY”表格是每一个用户端专属的表格，用户端离线后，MySQL就会自动删除这些表格 * 因为“TEMPORARY”表格是用户端专属的表格，其它用户端不能使用，所以不同的用户端，使用同样名称建立“TEMPORARY”表格也没有关系 * “TEMPORARY”表格名称可以跟数据库中的表格名称一样，不过在“TEMPORARY”表格存在的时候，数据库中的表格会被隐藏起来 * 可以使用“ALTER TABLE”修改“TEMPORARY”表格名称，不可以使用“RENAME TABLE”修改“TEMPORARY”表格名称 # 2 修改表格 使用“CREATE TABLE”叙述建立表格以后，如果发现某个字段或设定打错，或是在使用一阵子以后，发觉表格中有一些设定不太对。在这些情况下，你可以使用“ALTER TABLE”叙述来修改一个表格的结构： [](http://box.kancloud.cn/2015-09-15_55f7e9bf6a030.png) ## 2.1 增加字段 你可以使用下列的修改定义增加一个本来没有的字段： [](http://box.kancloud.cn/2015-09-15_55f7e9bfb77d8.png) 如果你在增加字段的时候，没有指定新增字段的位置，MySQL会把这个字段放在最后一个： [](http://box.kancloud.cn/2015-09-15_55f7e9bfebdf3.png) 你可以搭配使用“FIRST”关键字，把新增的字段放在第一个： [](http://box.kancloud.cn/2015-09-15_55f7e9c069f0a.png) 或是使用“AFTER”关键字，指定新增的字段要放在哪一个字段后面： [](http://box.kancloud.cn/2015-09-15_55f7e9c126aa3.png) 如果需要增加多个字段的话，也可以使用下列的语法一次把需要新增的字段，全部加到表格中；不过这种语法加入的新字段，都会放在最后面的位置： [](http://box.kancloud.cn/2015-09-15_55f7e9c1cd399.png) 注：“ALTER TABLE”叙述也可以用来增加索引，在这一章后面索引的部份一起讨论。 ## 2.2 修改字段 如果需要修改字段的名称、型态、大小范围或其它字段属性，你可以使用下列两种修改定义来执行修改的工作。“CHANGE”可以修改字段的名称与定义，“MODIFY”只能修改字段的定义，不能修改字段名称： [](http://box.kancloud.cn/2015-09-15_55f7e9c28a4d6.png) 以下列使用“CHANGE”关键字修改表格的叙述来说，它将“one”字段的名称修改为“changecolumn”，型态从“INT”修改为“BIGINT”，而且把修改后的字段位置放在“two”字段后面： [](http://box.kancloud.cn/2015-09-15_55f7e9e0eb944.png) 下列使用“MODIFY”关键字修改表格的叙述，它将“two”字段的型态从“INT”修改为“BIGINT”，而且把修改后的字段位置放在“three”字段后面： [](http://box.kancloud.cn/2015-09-15_55f7e9e189a4a.png) ## 2.3 删除字段 如果要删除一个表格中不需要的字段，可以使用下列的修改定义： [](http://box.kancloud.cn/2015-09-15_55f7e9ece038d.png) 下列格的叙述会删除“two”字段： [](http://box.kancloud.cn/2015-09-15_55f7e9f71f1dd.png) ## 2.4 修改表格名称 如果需要修改表格的名称，你可以使用下列两种叙述，包含在“ALTER TABLE”叙述中使用修改表格名称的修改定义；或是使用“RENAME TABLE”叙述： [](http://box.kancloud.cn/2015-09-15_55f7e9f7af503.png) 下列两个叙述都可以把“mytable”表格名称修改为“mynewtable”： [](http://box.kancloud.cn/2015-09-15_55f7e9f86275c.png) # 3 删除表格 你可以使用下列的叙述删除一个不需要的表格： [](http://box.kancloud.cn/2015-09-15_55f7e9f90fb7d.png) 注：使用“DROP TABLE”叙述执行删除表格的工作时，MySQL并不会再次跟你确认是否真的要删除，而是真的就直接删除了，表格储存的纪录资料当然也不见了。 # 4 索引介绍 数据库与表格是MySQL数据库的基本元件，依照需求建立好的数据库与表格后，就可以使用它们来为你保存资料。一个设计良好的数据库，不论是资料的正确性，还有后续的维护与查询都比较不会发生问题。除了好好规划与建立数据库与表格外，你还可以利用“索引、index”预防你的资料出现问题，尤其是表格储存非常大量的纪录时，建立适当的索引，可以增加查询与维护资料的效率。 以“MyISAM”储存引擎来说，资料表的储存的纪录资料，是储存在电脑中的一个档案： [](http://box.kancloud.cn/2015-09-15_55f7e9f986e02.png) 当你执行一个像这样的查询叙述时： [](http://box.kancloud.cn/2015-09-15_55f7e9f9e2355.png) 数据库要找到你需要查询或维护的纪录，如果没有索引帮助的话，就会从头开始一边读取，一边判断是否有符合条件的资料。你可以为表格建立索引来改善这种比较没有效率的方式： [](http://box.kancloud.cn/2015-09-15_55f7e9faa4155.png) 建立城市名称的索引档以后，同样执行下列的查询叙述，MySQL会自动使用索引来快速找到你需要的资料： [](http://box.kancloud.cn/2015-09-15_55f7ea053b854.png) 注：索引同样可以增加删除或修改的效率。 索引分为主索引键(primary key)、唯一索引(unique index)与非唯一索引(non-unique index)三种。 主索引键的应用很常见，而且一个表格通常会有一个，而且只能有一个。在一个表格中，设定为主索引键的字段值不可以重复，而且不可以储存“NULL”值。因为这样的限制，所以很适合使用在类似编码、代号或身份证字号这类字段。 唯一索引也称为“不可重复索引”，在一个表格中，设定为唯一索引的字段值不可以重复，但是可以储存“NULL”值。这种索引适合用在类似员工资料表格中储存电子邮件帐号的字段，因为员工不一定有电子邮件帐号，所以允许储存“NULL”值，可以每一个员工的电子邮件帐号都不可以重复。 上列两种索引都可以预防储存的资料发生重复的问题，也可以增加查询与维护资料的效率。非唯一索引就只是用来增加查询与维护资料效率的索引。设定为非唯一索引的字段值可以重复，也可以储存“NULL”值。 # 5 建立索引 MySQL提供许多不同的方式让你建立需要的索引。通常在规划一个数据库的时候，会把表格所需要的索引一并规划好，在这样的情况下，你可以把建立索引的定义，加在“CREATE TABLE”叙述中，建立表格的时候就一起把索引建立好；不过也有可能在使用表格一阵子以后，才发觉有建立索引的需求，在这样的情况下，你可以使用“ALTER TABLE”或“CREATE INDEX”建立需要的索引。 ## 5.1 在建立表格的时候建立索引 在建立表格的叙述中，你会定义出许多表格所需要的字段，在字段的定义中，除了名称、型态与属性，还可以加入“唯一索引”与“主索引键”的定义： [](http://box.kancloud.cn/2015-09-15_55f7ea106abb5.png) 以下列这个建立储存联络簿的表格来说，你可以使用这样的语法在“id”字段后面加入“PRIMARY KEY”，指定“id”字段为主索引键，这表示“id”字段的值不可以重复，而且不可以储存“NULL”值；另外在“email”字段加入“UNIQUE KEY”，指定“email”字段为唯一索引，这表示“email”字段的值不可以重复： [](http://box.kancloud.cn/2015-09-15_55f7ea10dc464.png) 下列是另外一种在“CREATE TABLE”叙述中建立索引的语法： [](http://box.kancloud.cn/2015-09-15_55f7ea2047a64.png) 同样以建立储存联络簿的表格来说，下列两种建立索引语法的效果是一样的： [](http://box.kancloud.cn/2015-09-15_55f7ea21091ed.png) 如果你要建立一般索引(可以重复的索引)，或是要建立包含多个字段的索引时，就一定要把建立索引的定义加在所有字段定义后面： [](http://box.kancloud.cn/2015-09-15_55f7ea21a0879.png) 在建立索引的时候，你可以指定某一个字段为建立索引的字段，不过有时候你只想要为一个字串型态字段的部份资料建立索引，或是指定建立的索引资料，是要依照由小到大，还是由大到小排列。有这样的需求时，你可以依照下列的语法来指定： [](http://box.kancloud.cn/2015-09-15_55f7ea2286e33.png) 以建立联络簿的表格来说，为地址资料“address”字段建立索引的时候，如果你希望建立地址前五个字符的索引资料，而且依照由大到小的顺序。下列的叙述就可以建立这样的索引： [](http://box.kancloud.cn/2015-09-15_55f7ea232ed70.png) 注：只有“CHAR”、“VARCHAR”、“BINARY”与“VARBINARY”型态的字段可以指定制作索引的长度。“ASC”或“DESC”可以使用在任何型态的字段。 如果一个表格使用的储存引擎是“MEMORY”的话，建立索引的时候还可以额外指定索引使用的“算法、algorithm”。使用其它储存引擎的表格，MySQL会忽略这个设定。索引使用的算法有“BTREE”与“HASH”两种，你可以使用下列的语法来指定索引使用的算法： [](http://box.kancloud.cn/2015-09-15_55f7ea28d2db9.png) 默认的“HASH”算法适合用在主索引键和唯一索引，这种算法在搜寻不能重复的资料时，效率会比较好；而“BTREE”算法适合用在可以允许重复资料的一般索引，在搜寻上会比“HASH”有更好的效率。 注：“FULLTEXT”索引只能用在“CHAR”、“VARCHAR”与“TEXT”型态的字段，而且表格使用的储存引擎必须是“MyISAM”。“SPATIAL”索引是“SPATIAL”型态字段专用的，而且表格使用的储存引擎必须是“MyISAM”。这两种索引不会在这里讨论。 ## 5.2 在修改表格的时候建立索引 如果你想要为一个已经存在的表格建立索引的话，你可以在修改表格“ALTER TABLE”中建立索引： [](http://box.kancloud.cn/2015-09-15_55f7ea2eb0db8.png) 以下列的范例来说，在建立联络簿表格时没有建立索引，你可以使用“ALTER TABLE”叙述建立需要的索引，不过一个“ALTER TABLE”叙述只能建立一个索引： [](http://box.kancloud.cn/2015-09-15_55f7ea354b0f0.png) ## 5.3 使用“CREATE INDEX”建立索引 需要为一个已经存在的表格建立索引，除了使用“ALTER TABLE”叙述建立索引外，还可以使用“CREATE INDEX”叙述建立唯一索引与一般索引： [](http://box.kancloud.cn/2015-09-15_55f7ea363c177.png) 使用“CREATE INDEX”叙述只能建立唯一索引与一般索引，你还是要使用“ALTER TABLE”叙述建立主索引键： [](http://box.kancloud.cn/2015-09-15_55f7ea354b0f0.png) 为一个已经存在的表格建立索引时，要特别注意主索引键与唯一索引这两种索引。如果这个表格没有任何纪录资料的话，那就不会有问题；可是如果表格中已经有纪录了，而且你想要建立一个主索引键时，有可能会发生下列的错误： [](http://box.kancloud.cn/2015-09-15_55f7ea3c14d37.png) 为一个已经存在、而且已经有纪录的表格建立唯一索引时，也有可能会发生下列的错误： [](http://box.kancloud.cn/2015-09-15_55f7ea3ca1c31.png) # 6 索引的名称 在“CREATE TABLE”或是“ALTER TABLE”叙述中建立索引的话，你可以为建立的索引取一个名称： [](http://box.kancloud.cn/2015-09-15_55f7ea3ce4c97.png) 如果你在使用上列的语法建立索引的时候没有指定索引名称，MySQL会帮你取一个，索引的名称就是字段名称，如果是多个字段的索引，就会使用第一个字段当作索引名称。 使用“CREATE INDEX”建立索引的时候，就一定要指定一个索引名称： [](http://box.kancloud.cn/2015-09-15_55f7ea3da6ea8.png) 注：在一般的操作中，你并不会用到索引名称；不过在删除索引的时候就会用到。 # 7 删除索引 如果一个已经建立好的索引已经不需要了，为了节省储存的空间，你可以使用下列的语法删除索引： [](http://box.kancloud.cn/2015-09-15_55f7ea3ddb46f.png) 下列的叙述使用修改表格“ALTER TABLE”叙述删除不需要的索引： [](http://box.kancloud.cn/2015-09-15_55f7ea3e4a71a.png) 你也可以使用下列的“DROP INDEX”叙述删除不需要的索引： [](http://box.kancloud.cn/2015-09-15_55f7ea43a7a7e.png) 使用“ALTER TABLE”叙述可以一次删除多个索引，“DROP INDEX”叙述一次只能删除一个索引： [](http://box.kancloud.cn/2015-09-15_55f7ea442b13b.png) # 8 数值字段型态与AUTO\_INCREMENT 在数据库的应用中，很常会遇到为纪录“编流水号”的需求，如果资料表中的每一笔纪录都需要一个递增的数值编号，你可以选择整数型态的字段后，再使用“AUTO\_INCREMENT”字段属性： [](http://box.kancloud.cn/2015-09-15_55f7ea446528c.png) 如果一个公司想要储存员工开会的资料，你可以在建立开会资料表格的时候，为这个表格定义一个储存开会编号的字段，这个字段需要自动递增，而且会为它建立主索引键： [](http://box.kancloud.cn/2015-09-15_55f7ea44e0c67.png) 建立开会资料表格以后，另外建立一个储存参加会议的员工资料表格： [](http://box.kancloud.cn/2015-09-15_55f7ea4a75fcf.png) 设定为“AUTO\_INCREMENT”的整数字段，在新增资料的时候可以不用指定数值，MySQL会为你自动编制一个流水号并储存在纪录中；而接着要新增参加这次开会的员工资料到“participate”表格时，你需要用到MySQL刚才会为你在“meeting”表格中自动编制的流水号，这样的需求可以使用“LAST\_INSERT\_ID()”函式来取得： [](http://box.kancloud.cn/2015-09-15_55f7ea4bd83ed.png) 新增这些开会与参加会议的员工资料后，就可以使用结合查询来查询开会资料了： [](http://box.kancloud.cn/2015-09-15_55f7ea4c90188.png) 在新增资料时，要让MySQL为你自动编制一个流水号，并储存到纪录中的方式有下列几种： [](http://box.kancloud.cn/2015-09-15_55f7ea4d8ac4e.png) MySQL是一个可以让多人同时使用的数据库，使用“LAST\_INSERT\_ID()”函式来取得自动编制的流水号数值，并不会因为不同的用户端同时使用而造成混乱： [](http://box.kancloud.cn/2015-09-15_55f7ea4df3e59.png) “AUTO\_INCREMENT”字段的一般用法通常是用来储存从“1”开始的流水号，每一笔新增的纪录都会自动加一成为新的编号。可是如果在新增纪录的时候，自己指定“AUTO\_INCREMENT”字段一个数值，就会造成下列的情况： [](http://box.kancloud.cn/2015-09-15_55f7ea4e8ba96.png) “AUTO\_INCREMENT”字段在你删除纪录以后，也不会帮你重新使用已经用过的编号： [](http://box.kancloud.cn/2015-09-15_55f7ea4f1e5fc.png) 注：使用“TRUNCATE TABLE”叙述删除包含“AUTO\_INCREMENT”字段表格的所有纪录，编号会重新从头开始。 不要指定值，或是指定“NULL”值给“AUTO\_INCREMENT”字段，都可以让MySQL为你自动编制一个流水号，并储存到纪录中，这两种也是比较好的方式；另外指定“AUTO\_INCREMENT”字段值为“0”的方式也可以，不过会因为MySQL数据库服务器的环境设定而有不同的效果： [](http://box.kancloud.cn/2015-09-15_55f7ea4fa60c4.png) 如果你需要编制的流水号范围是非常大的，你应该选择“AUTO\_INCREMENT”字段的型态为“BIGINT”；MySQL另外提供一个“SERIAL”关键字，让你在定义这种字段时可以比较方便一些： [](http://box.kancloud.cn/2015-09-15_55f7ea5023fd5.png) 使用“MyISAM”储存引擎的表格，可以使用下列这种比较特殊的“AUTO\_INCREMENT”字段： [](http://box.kancloud.cn/2015-09-15_55f7ea50a0f90.png) 这样的设定同样是请MySQL为你自动编制流水号，不过因为“AUTO\_INCREMENT”字段包含在主索引键中，编制流水号的动作会不太一样： [](http://box.kancloud.cn/2015-09-15_55f7ea560c753.png) 注：在上列的范例中，是把“empno,location,counter”设定为主索引键；如果设定为唯一索引的话，也会有一样的效果；设定为一般索引的话，会造成错误。 使用“AUTO\_INCREMENT”字段属性有下列几个重点： * 一个表格只能有一个“AUTO\_INCREMENT”字段，而且要为它建立一个索引，而且通常是建立主索引键或唯一索引，这样可以防止重复的编号；不过MySQL也允许你建立可重复的索引 * 只有整数型态才可以使用“AUTO\_INCREMENT”字段属性，你可以根据编号大小的需求，选择使用“TINYINT”、“SMALLINT”、“MEDIUMINT”、“INT”或“BIGINT”，而且因为只会使用到正数，所以你可以加入“UNSIGNED”来增加编号的范围 * 如果编号已经到字段型态的最大范围，例如一个“SMALLINT”型态，而且是指定为“UNSIGNED”的“AUTO\_INCREMENT”字段，编号已经到“65535”了，如果再执行新增的叙述，就会造成“Duplicate entry ’65535′ for key ‘字段名称’”的错误 # 9 查询表格与索引资讯 一个数据库在建立许多表格与索引以后，不论是程式开发或是数据库管理人员，都会有查询表格与索引相关资料的需求。例如查询一个表格中有哪些字段，还有字段的型态与属性的设定；也可能需要查询某一个表格建立了哪些索引与设定的资讯。 ## 9.1 表格相关资讯 想要知道一个数据库中有哪一些表格，可以执行下列的叙述： [](http://box.kancloud.cn/2015-09-15_55f7ea56d7854.png) 这个叙述可以使用“字串样式”设定表格名称的条件： [](http://box.kancloud.cn/2015-09-15_55f7ea5c43f56.png) MySQL数据库在启动以后，会有一个很特别的数据库，名称是“information\_schema”，这个数据库通常会称为“系统资讯数据库”。这个数据库中有一个表格叫作“TABLES”，它储存所有MySQL数据库中的表格相关资讯，“TABLES”表格有下列主要的字段： | 字段名称 | 型态 | 说明 | | --- | --- | --- | | TABLE\_SCHEMA | varchar(64) | 数据库名称 | | TABLE\_NAME | varchar(64) | 表格名称 | | ENGINE | varchar(64) | 使用的储存引擎名称 | | TABLE\_ROWS | bigint(21) unsigned | 纪录数量 | | AUTO\_INCREMENT | bigint(21) unsigned | 如果包含“AUTO\_INCREMENT”字段的话，这个字段会储存下一个编号 | | TABLE\_COLLATION | varchar(32) | 表格使用的collation | 执行下列的查询叙述就可以查询表格详细的资讯： [](http://box.kancloud.cn/2015-09-15_55f7ea5cb0146.png) MySQL也提供下列的叙述让你查询一个表格的定义： [](http://box.kancloud.cn/2015-09-15_55f7ea5d3e7e3.png) 下列的叙述可以查询建立表格的“CREATE TABLE”叙述： [](http://box.kancloud.cn/2015-09-15_55f7ea6290068.png) 回传的“Create Table”字段的内容就是一个建立表格的叙述： [](http://box.kancloud.cn/2015-09-15_55f7ea63022c4.png) ## 9.2 索引相关资讯 MySQL提供“SHOW INDEX”叙述查询一个表格的索引详细资讯，下列是执行这个叙述以后，传回的主要字段资料： | 字段名称 | 说明 | | --- | --- | | Table | 表格名称 | | Non\_unique | “0”表示不可重复；“1”可以重复 | | Key\_name | 索引名称 | | Seq\_in\_index | 单一字段的索引为“1”；多个字段的索引表示建立索引的字段顺序 | | Column\_name | 索引字段名称 | | Sub\_part | 如果是指定长度的索引，这里会显示长度；不是的话显示“NULL” | | Null | 是否允许“NULL”值 | | Index\_type | 索引种类，“BTREE”或“HASH” | 你可以在“SHOW INDEX FROM”后面指定一个表格名称，执行以后就可以查询这个表格所有的索引资讯： [](http://box.kancloud.cn/2015-09-15_55f7ea6393f9d.png)