利用MySQL发展数据库-系统运维-优客志

随着MySQL数据库的发展

Willem Bogaerts - 克拉茨商业解决方案

概要

在当今的编程中，数据存储远离一些人曾经认为的静态野兽。许多应用程序在关系数据库中都有自己的数据存储，并且存储器的形状像应用程序本身一样频繁地变化：每天。唯一的问题是，虽然结构必须经常改变，但是数据本身就不得不在其周围变化，并且基本上保持原样，而是以新的形式。

在这个howto中，我会告诉你如何以可维护的方式“增长”你自己的数据库定义。我会做所谓的“重现”脚本。这些脚本可用于从头创建数据库，并更新现有的数据库。当我说数据库定义时，它不会局限于表结构，我们也会“增长”数据。但不是全部。只有必要的
为什么重新创建脚本？我经常需要一个干净的状态，我不想只是复制一个现有的数据库来清理它。这些情况包括在测试服务器上滚动，在家工作，或客户要求我们为新的外国办公室制作版本。另一方面，我确实希望我的数据库能够生存下去。即使在我的开发机器上。我已经输入了很多有用的测试数据，我不想每隔一段时间开始一个干净的数据库。当然，现场情况也是无痛地更新的。一个可重复的脚本可以帮助你甚至在那里：如果出现问题，只需修复脚本中的错误即可重新运行它。您不必首先做一个痛苦的回滚（结构更改不能由事务语句回滚）。
有趣的是，您是否进行敏捷开发并不重要。如果您以敏捷的方式发展，您将重视数据库的发展，作为一个自然过程，但如果您在瀑布下滑，您也将看到光线。应用程序发展，数据库将随之发展。不管你喜不喜欢。

这个方法涵盖了我自己经验的几年。我发现它很有用，可以分享它，对你来说可能不太有用。即使如此，它可能会帮助您思考自己的发展方式，并发现新的光芒。这不是唯一的办法，但是我可以在两分钟内以受控的方式更新网站，其他人会在两个小时内完成，并且没有任何问题。

当我与前雇主工作时，我们从未在星期五更新过一个网站，因为担心我们不得不牺牲一个周末的修复。现在，我可以在与客户打电话时轻松更新网站。

数据类型

有许多类型的数据，当分类到必须做的事情。我认识到以下几种数据：

定义数据: 定义数据由系统数据（程序运行所需）和查找表组成。定义数据通常索引良好，特别是系统数据必须与应用程序“成长”。也可以增加一些通用查找数据，例如国家列表。定义数据在程序运行过程中几乎不会发生变化，如果有的话，在程序启动时将数据加载到内存中，并将其保存在内存中是很安全的。一些是设置，它们必须创建，如果它们不存在，但不会改变的时候。
实时数据: 在应用程序运行期间，实时数据更改定义，因此是程序员面临的最大挑战。实时数据迫使您对多用户系统使用事务和各种检查。它们不会存储在重新创建的脚本中。但是，重新创建脚本必须考虑到实时数据的存在，有时您将必须提供一个存储的例程来进行升级。
测试数据: 测试数据分为3类：最初提供客户端播放数据或用于演示目的，我的开发机器上的个人实时数据和通用测试数据。通用测试数据是测试数据，这对于任何开发或测试安装都是无用的。所以它也将在重新创建脚本，但在一个单独的文件，不会在实时安装上运行。请注意，数据类型可以更改。例如，“初始数据”可以成为通用测试数据。
日志数据: 日志数据主要存储，几乎不读取。某些日志数据可能并不重要，以保持永远，如错误日志。一旦阅读日志并修复错误，您可以清理它。一般来说，日志是错误的，有时没有索引的速度。日志发展很容易，因为这些日志不会经常发展，只能增长，并且很少有数据必须升级。

上述类型的数据和与它们不同的动作是我不相信自动数据库同步系统的原因。

模块代码

模块化代码是保持代码可维护的必要条件。您的数据库“增长”的事实并不意味着它必须在一个项目中成长。片段可能来自标准库，以及使用它们的应用程序代码。
不幸的是，SQL本身并不完全是模块化的。有两种方法可以解决这个问题：通过使用mysql命令行客户端的SOURCE命令，或者使用外部程序作为预处理器。就我个人而言，我更喜欢预处理方式，因为它可以与MySQL命令行客户机之类的其他程序一起使用，并且使错误行数值有意义。您可以下载我使用的预处理脚本：一个在命令行PHP中，一个在Python中。
模块化代码允许您将大块SQL放在不同的文件中。因此，您可以将翻译放在自己的文件中，另一个中的对象关系映射设置，具有来自标准库的包的特殊文件等。没有这一点，维护SQL几乎不可行。主脚本可能如下所示：

CREATE DATABASE IF NOT EXISTS YourDatabase DEFAULT CHARSET utf8;
USE YourDatabase;
FLUSH TABLES; -- See remark
-- From the standard library:
-- @include(errorhandling/recreate.sql) Definition for error log tables.
-- @include(orm/recreate.sql) Object Relational Mapping tables
-- @include(language/recreate.sql)
-- @include(language/ormsettings.sql)
-- @include(language/standarddata.sql)
-- @include(template/recreate.sql)
-- @include(template/ormsettings.sql)
-- Project specific definitions:
CREATE TABLE IF NOT EXISTS Translation
      (intTranslationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       intLanguageId INTEGER UNSIGNED NOT NULL,
       vchKeyword VARCHAR(100) NOT NULL,
       txtTranslation TEXT,
       INDEX idxLanguageid(intLanguageId),
       UNIQUE INDEX idxKeywordLanguageid(vchKeyword,intLanguageId),
       CONSTRAINT fkyTranslation_LanguageId FOREIGN KEY(intLanguageId)
               REFERENCES UserLanguage(intUserLanguageId)
               ON DELETE RESTRICT
               ON UPDATE RESTRICT );
-- @Include(specific/translations.sql) Large file
-- @include(specific/ormsettings.sql) Large file
-- @include(specific/templatedata.sql) Generated file. Generated from template files.
-- @include(specific/grants.sql) Comes last: the referred tables must exist. See also Caveat.

下降和重建

使脚本可重复的最简单的形式是删除表，如果它们存在并重新创建。在MySQL 5.0之前，这也是唯一的办法。使用各种CREATE和DROP命令中的IF（NOT）EXISTS子句。对于某些表，如前面提到的错误日志，甚至可能是首选方法：

DROP TABLE IF EXISTS ErrorLog;
CREATE TABLE ErrorLog
      (intErrorLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       txtMessage TEXT,
       dtmWhen DATETIME NOT NULL )
ENGINE=InnoDB;

但是大多数时候，你不希望这些表被丢弃，因为它们也丢失了所有的数据。因此，这不是包含实时数据的表的选项。稍后将会介绍如何使用存储过程来保存数据。

排序外键

一个重要的事情要记住，任何外键都必须指向一个现有的主键。因此，在引用的表之前删除从属表，并在创建依赖于它的表之前创建引用的表：

DROP TABLE IF EXISTS ErrorStackTrace; -- Depends on ErrorLog
DROP TABLE IF EXISTS ErrorLog;
CREATE TABLE ErrorLog
      (intErrorLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       txtMessage TEXT,
       dtmWhen DATETIME NOT NULL )
ENGINE=InnoDB;
CREATE TABLE ErrorStackTrace
      (intErrorStackTraceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       intErrorLogId INTEGER UNSIGNED NOT NULL,
       intLine INTEGER UNSIGNED DEFAULT NULL,
       vchFile VARCHAR(150) DEFAULT NULL,
       INDEX idxErrorLogId(intErrorLogId),
       CONSTRAINT fkyErrorStackTrace_ErrorLogId FOREIGN KEY(intErrorLogId)
               REFERENCES ErrorLog(intErrorLogId)
               ON DELETE CASCADE
               ON UPDATE CASCADE )
ENGINE=InnoDB;

当我们在这里，尝试习惯给所有的东西命名。稍后，当我们访问存储过程时，您将需要它。索引名称在表中只能是唯一的，但外键约束在整个数据库中必须是唯一的。我完全知道这会导致使用非常丑的约束名称。只有主键不需要名称，因为它在MySQL中一直被称为“PRIMARY”。

关系

特别是进化数据库开发迫使你定义你的表。把这些唯一性约束放在你需要的地方，不要忘记命名你定义的所有东西。这包括索引和外键。当将测试数据甚至将系统数据放入te数据库时，这将大有裨益。
当然，没有固定的主键值（您可以使用autonumbers作为主键，我希望），除非您在代码中定义表示枚举的表。
MySQL有两个很好的特性可以与关系使用： LAST_INSERT_ID()函数和SQL变量。让我提醒您，只要连接到位，变量就会保持定义：您可以在整个脚本中使用这些变量。一个例子：

CREATE TABLE IF NOT EXISTS UserLanguage
      (intUserLanguageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       vchIsoCode VARCHAR(7) NOT NULL COMMENT 'Two-letter code with dialect',
       vchName VARCHAR(100) NOT NULL COMMENT 'in the language itself',
       UNIQUE INDEX idxIsoCode(vchIsoCode),
       UNIQUE INDEX idxName(vchName) )
ENGINE=InnoDB;
INSERT INTO UserLanguage(vchIsoCode,vchName)
       VALUES('NL', 'Nederlands')
       ON DUPLICATE KEY UPDATE intUserLanguageId=LAST_INSERT_ID(intUserLanguageId),
                               vchName=VALUES(vchName);
SET @DutchId=LAST_INSERT_ID();

INSERT .. ON DUPLICATE KEY结构将在下面更详细的介绍，但请注意， LAST_INSERT_ID()函数用于将最后插入的ID设置为正确的值，以防记录已存在。
我们现在可以使用@DutchID，无论我们想要荷兰语语言记录的ID。
不用使用LAST_INSERT_ID() ，也可以使用SELECT .. INTO语句设置变量：

SELECT intUserLanguageId INTO @DutchId FROM UserLanguage WHERE vchIsoCode='NL';

插入和更新

MySQL对标准的INSERT和UPDATE命令有一些很好的补充。许多命令kan具有IGNORE子句，并且INSERT命令kan具有ON DUPLICATE KEY子句。这些子句非常有用，以确保某些数据存在（例如设置）或设置为某个值（对于系统数据）。 INSERT命令也可用于一次插入多行，这可以增强代码的易读性。当然， LAST_INSERT_ID()只能返回一个值，所以如果你以后想要的话，你必须查询任何批量插入的值。

确保记录存在：INSERT IGNORE

这是一个很有用的设置。以下示例确保某些设置存在默认值，但该值不存在并被更改时将被覆盖：

CREATE TABLE IF NOT EXISTS Setting
      (intSettingId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       vchName VARCHAR(100) NOT NULL,
       txtValue TEXT,
       UNIQUE INDEX idxName(vchName) )
ENGINE=InnoDB;
INSERT IGNORE INTO Setting(vchName,txtValue)
       VALUES('ThumbnailWidth', '32'),
             ('ThumbnailHeight', '32'),
             ('PasswordHashSalt', 'Something');

上述代码段可以随意运行，因为IF NOT EXISTS子句只会创建一次表， IGNORE子句将仅插入一次数据。这是因为设置名称字段存在唯一性约束。 IGNORE子句意味着：当它违反约束时，默认忽略此插入。它不是一般的错误抑制器：如果脚本包含语法错误，脚本仍然会失败。但这没有问题，是吗？只需修复该错误并重新运行脚本。这就是为什么它是可重复的。好的是， IGNORE子句是按行逐行的：每行都被检查一个约束违规，只有违反的行被忽略。

唉， DELETE IGNORE命令不是这样。该命令对于删除不违反外键约束的行很有用，但是一个违规将会停止整个命令。所以DELETE IGNORE最好用于循环中的存储过程。

确保字段有正确的值：INSERT .. ON DUPLICATE KEY

通常，我们需要一些数据才能使应用程序正常工作。考虑对象关系映射，程序标题，选择列表等。这些数据也必须具有正确的值，如果具有另外的值，则必须进行更新。例如，选项表可能如下所示：

CREATE TABLE IF NOT EXISTS SelectionOption
      (intSelectionOptionId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       vchSelection VARCHAR(50) NOT NULL,
       vchOptionValue VARCHAR(50) DEFAULT NULL,
       vchOptionCaption VARCHAR(150) NOT NULL,
       UNIQUE INDEX idxSelectionValue(vchSelection,vchOptionValue),
       UNIQUE INDEX idxSelectionCaption(vchSelection,vchOptionCaption) )
ENGINE=InnoDB;
INSERT INTO SelectionOption(vchSelection,vchOptionValue,vchOptionCaption)
       VALUES('boolean', 'false', 'No'),
             ('boolean', 'true', 'Yes'),
             ('tristate', 'false', 'No'),
             ('tristate', 'true', 'Yes'),
             ('tristate', NULL, 'Unknown')
       ON DUPLICATE KEY UPDATE vchOptionCaption=VALUES(vchOptionCaption);

请注意，我们在这里有两个唯一性约束：一个“结构”（没有两个选项可以评估选择中的相同值）和一个“行为”：用户必须能够看到选择中的任何两个选项之间的差异。这是一种情况，如果同一选择中的两个不同选项具有相同的标题，则可能会故意失败。这是可以的，因为我们不希望在我们的数据库中出现这种情况。
那么如果您运行并重新运行上述代码段，会发生什么？如果选项不存在，则添加它们。如果它们确实存在，则违反第一（结构）唯一性约束，并且仅更新标题。这允许我们改变上述语句中的标题，并且每当运行脚本时，它将在数据库中得到纠正。如果新标题与同一选择中的任何其他标题不同，则此更新将成功。如果没有，我们有修复的东西，脚本会失败。没问题。只需修复标题并重新运行脚本。

在ON DUPLICATE KEY子句中有两个特殊功能： LAST_INSERT_ID()和VALUES() 。 VALUES()函数返回如果没有约束违例将被插入的值，这是我们提供的值。如您在代码片段中所示，这对于多行插入来说尤其有用。请注意，这也适用于INSERT .. SELECT语句。
可以使用LAST_INSERT_ID()函数没有参数来获取最后插入的自动编号，但是如果提供一个整数，则将设置该值，返回值，直到INSERT命令或其他LAST_INSERT_ID调用更改它为止 。这可以与ON DUPLICATE KEY子句结合使用：如果行不存在（不违反唯一性约束），则记录被插入，并且ON DUPLICATE KEY部分不会为该记录运行。但是，如果记录存在，则ON DUPLICATE KEY部分将运行，您可以将LAST_INSERT_ID函数与现有主键值进行馈送：

INSERT INTO UserLanguage(vchIsoCode,vchName)
       VALUES('NL', 'Nederlands')
       ON DUPLICATE KEY UPDATE intUserLanguageId=LAST_INSERT_ID(intUserLanguageId),
                               vchName=VALUES(vchName);

在此语句之后， LAST_INSERT_ID()函数返回荷兰语记录的ID，因为它刚被插入，或者因为LAST_INSERT_ID函数被设置为它。记住，如果在ON DUPLICATE KEY子句中使用一个字段，则使用现有值。用VALUES（ <field> ）使用新值。

它是所有在信息_schema

自从MySQL 5以来，有一个数据库包含几乎所有有关其他数据库的信息： information_schema 。所以如果你想检查列是否已经定义，你可以查找。 Windows中有一个警告：如果在安装MySQL后，直接在my.ini中将lower_case_table_names设置为2，则数据库名称可以以小写形式显示。因为大多数脚本也必须在Windows上运行，所以我考虑到这一点。当然，如果你想查找一些东西，它必须有一个名字。这就是为什么每个索引和每个外键都必须有一个名称：否则，很难检查它，甚至更难删除它。

唉我们需要他们：存储例程

到目前为止，MySQL已经极大地帮助了我们IGNORE和IF EXISTS子句。添加或删除列时，这些不适用。没有像ALTER TABLE .. DROP COLUMN IF EXISTS这样的东西。这意味着我们必须查询information_schema数据库并对其内容采取行动。由于我们不能在存储例程之外使用IF语句，因此定义临时过程是有意义的。

例如，让我们假设客户希望能够在早期给出的SelectionOption表中设置默认选项。我们决定通过提取Selection表并使SelectionOption表依赖于它来完成。我们将创建一个“反向引用”：选择选项参考选择，但是选择表也有一个引用默认选项的列：

CREATE TABLE IF NOT EXISTS Selection
      (intSelectionId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       vchSelection VARCHAR(50) NOT NULL,
       intDefaultOption INTEGER DEFAULT NULL, -- Cannot have a foreign key constraint.
       UNIQUE INDEX idxSelection(vchSelection) )
ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS SelectionOption
      (intSelectionOptionId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       intSelectionId INTEGER UNSIGNED NOT NULL,
       vchOptionValue VARCHAR(50) DEFAULT NULL,
       vchOptionCaption VARCHAR(150) NOT NULL,
       UNIQUE INDEX idxSelectionIdValue(intSelectionId,vchOptionValue),
       UNIQUE INDEX idxSelectionIdCaption(intSelectionId,vchOptionCaption) )
ENGINE=InnoDB;
DROP PROCEDURE IF EXISTS UpgradeSelectionOption;
DELIMITER //
CREATE PROCEDURE UpgradeSelectionOption()
       MODIFIES SQL DATA
       COMMENT 'Last modified: 20090924' -- See remark about cleaning up.
       BEGIN
       IF 0=(SELECT COUNT(0) FROM information_schema.COLUMNS
                    WHERE UPPER(TABLE_SCHEMA)=UPPER(DATABASE()) -- See remark about lower_case_table_names
                          AND TABLE_NAME='SelectionOption'
                          AND COLUMN_NAME='intSelectionId' ) THEN
          -- First, fill the new Selection table:
          INSERT IGNORE INTO Selection(vchSelection)
                 SELECT DISTINCT vchSelection FROM SelectionOption; -- This field still exists.
          -- Add te new column, but allow it to be empty:
          ALTER TABLE SelectionOption
                ADD COLUMN intSelectionId INTEGER UNSIGNED DEFAULT NULL
                AFTER intSelectionOptionId;
          -- Fill the new column:
          UPDATE SelectionOption INNER JOIN Selection ON SelectionOption.vchSelection=Selection.vchSelection
                 SET SelectionOption.intSelectionId=Selection.intSelectionId;
          -- Now we can safely disallow empty values.
          ALTER TABLE SelectionOption
                MODIFY COLUMN intSelectionId INTEGER UNSIGNED NOT NULL;
          -- Repair the indexes:
          ALTER TABLE SelectionOption
                DROP INDEX idxSelectionCaption,
                DROP INDEX idxSelectionValue,
                ADD UNIQUE INDEX idxSelectionIdValue(intSelectionId,vchOptionValue),
                ADD UNIQUE INDEX idxSelectionIdCaption(intSelectionId,vchOptionCaption);
          -- Add the relation:
          ALTER TABLE SelectionOption
                ADD CONSTRAINT fkySelectionOption_SelectionId FOREIGN KEY(intSelectionId)
                            REFERENCES Selection(intSelectionId)
                            ON DELETE CASCADE
                            ON UPDATE CASCADE;
          -- Finally, drop the old column:
          ALTER TABLE SelectionOption
                DROP COLUMN vchSelection;
       END IF;
       END//
DELIMITER ;
CALL UpgradeSelectionOption();
DROP PROCEDURE UpgradeSelectionOption;
INSERT IGNORE INTO Selection(vchSelection)
       VALUES('boolean'),
             ('tristate');
SELECT intSelectionId INTO @SelectionId FROM Selection WHERE vchSelection='boolean';
INSERT INTO SelectionOption(intSelectionId,vchOptionValue,vchOptionCaption)
       VALUES(@SelectionId, 'false', 'No'),
             (@SelectionId, 'true', 'Yes')
       ON DUPLICATE KEY UPDATE vchOptionCaption=VALUES(vchOptionCaption);
SELECT intSelectionId INTO @SelectionId FROM Selection WHERE vchSelection='tristate';
INSERT INTO SelectionOption(intSelectionId,vchOptionValue,vchOptionCaption)
       VALUES(@SelectionId, 'false', 'No'),
             (@SelectionId, 'true', 'Yes'),
             (@SelectionId, NULL, 'Unknown')
       ON DUPLICATE KEY UPDATE vchOptionCaption=VALUES(vchOptionCaption);

我故意选择了一个涉及修复关系的例子。如你所见，由于依赖关系，在SelectionOption表之前定义了新的Selection表。
当第一次运行此脚本时，将创建表并运行存储过程。存储过程中的第一个语句看到intSelectionId列已经存在，并且将跳过升级。之后，确保数据在新结构中存在。
如果在运行脚本之前存在早期示例的SelectionOption表，则“Selection”表由第一个CREATE TABLE语句创建 。第二个CREATE TABLE语句将不会执行任何操作，因为“SelectionOption”表已经存在。存储过程将运行，注意缺少“intSelectionId”列，并执行升级。之后，确保数据在新结构中存在。请注意，如果表格包含超过scipt中给出的数据，那么现有数据将被很好地迁移。

提示：为了清楚起见，我在上面的例子中只使用了一个IF语句。实际上，我将为列使用IF语句，一个用于索引，一个用于外键约束。这样做的原因是升级可能会失败，并且在错误更正后，重新运行该脚本应该会进行修复。

过程模板

存储程序相当强大，需要学习才能充分发挥潜力。让我给你一些可以用于最基本的任务的模板，比如添加或删除列，索引和外键。在你喜欢的程序员的编辑器中使用这样的模板，编写简单的程序很容易。
第一个模板是一个空的临时过程：

DROP PROCEDURE IF EXISTS Upgrade<TableName>;
DELIMITER //
CREATE PROCEDURE Upgrade<TableName>()
       MODIFIES SQL DATA
       COMMENT 'Last modified: '
       BEGIN
       -- Your magic goes here...
       END//
DELIMITER ;
CALL Upgrade<TableName>();
DROP PROCEDURE Upgrade<TableName>;

这个片段中的“魔法”通常由下面三个片段之一组成。第一个是“缺少列”：它检查表中是否不存在列。请注意，以不区分大小写的方式检查数据库名称，以解决Windows上涉及lower_case_table_names的错误：

IF 0=(SELECT COUNT(0) FROM information_schema.COLUMNS
             WHERE UPPER(TABLE_SCHEMA)=UPPER(DATABASE())
                   AND TABLE_NAME='<YourTableName>'
                   AND COLUMN_NAME='<YourColumnName>' ) THEN
   -- Your magic goes here ...
END IF;

而魔术通常只是一个ALTER TABLE命令，添加了mising列。如果要检查现有列，只需在SELECT查询之前将“0 =”替换为“1 =”或“0 <”。我们可以为缺失的索引做同样的事情：

IF 0=(SELECT COUNT(0) FROM information_schema.STATISTICS
             WHERE UPPER(TABLE_SCHEMA)=UPPER(DATABASE())
                   AND TABLE_NAME='<YourTableName>'
                   AND INDEX_NAME='<YourIndexName>' ) THEN
   -- Your magic goes here ...
END IF;

如果要添加唯一性约束，请使用ALTER IGNORE TABLE添加它。这样，违反新约束的行就会被静默地删除。
最后一个片段是为了缺少外键限制：

IF 0=(SELECT COUNT(0) FROM information_schema.TABLE_CONSTRAINTS
             WHERE UPPER(TABLE_SCHEMA)=UPPER(DATABASE())
                   AND TABLE_NAME='<YourTableName>'
                   AND CONSTRAINT_NAME='<YourConstraintName>') THEN
   -- Your magic goes here ...
END IF;

警告

SQL中紧密和松散的耦合

在示例主脚本中，我已经将翻译表取决于标准库中的语言包：

CREATE TABLE IF NOT EXISTS Translation
      (intTranslationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       intLanguageId INTEGER UNSIGNED NOT NULL,
       vchKeyword VARCHAR(100) NOT NULL,
       txtTranslation TEXT,
       INDEX idxLanguageid(intLanguageId),
       UNIQUE INDEX idxKeywordLanguageid(vchKeyword,intLanguageId),
       CONSTRAINT fkyTranslation_LanguageId FOREIGN KEY(intLanguageId)
               REFERENCES UserLanguage(intUserLanguageId)
               ON DELETE RESTRICT
               ON UPDATE RESTRICT )
ENGINE=InnoDB;

但是如果我想在标准库中编写一个翻译包呢？我可以使用完全相同的定义，但是翻译将始终取决于相同的语言表，这可能是不可取的。您可能需要编写一个也可以与其他语言表一起使用的翻译包，例如第三方CMS中的一个。为了做到这一点，我故意在包中省略了外键约束：

CREATE TABLE IF NOT EXISTS Translation
      (intTranslationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       intLanguageId INTEGER UNSIGNED NOT NULL,
       vchKeyword VARCHAR(100) NOT NULL,
       txtTranslation TEXT,
       INDEX idxLanguageid(intLanguageId),
       UNIQUE INDEX idxKeywordLanguageid(vchKeyword,intLanguageId) )
ENGINE=InnoDB;

如你所见，没有任何表名，没有字段名称被用于语言，所以我们可以使用任何表或甚至表的组合来提供语言ID。
这相当于编程语言中的松散耦合：指定一般依赖关系，以便稍后填写确切的细节。第一个定义是紧耦合的一个例子：一个完全定义并因此固定的依赖关系。
请注意，虽然标准库可能会省略外键检查以使程序包更普遍适用，但是没有任何阻止您在项目主脚本中添加约束。这些约束现在确实是项目特定的。

删除IGNORE

DELETE IGNORE不能像INSERT IGNORE那样逐行地运行。该命令可能有助于清理初始存在的测试或演示数据。如果选择单个记录，这可以正常工作。但是，通过选择多个记录，由于违反外键而无法删除的第一条记录将停止整个命令。

因此，最好在过程中删除多个记录。以下示例尝试删除由演示用户创建的所有公司。如果由于外键限制，该公司不能被删除，该公司可能仍在使用中，应该保持原样。
请注意，最后一个DELETE IGNORE将实际执行两次，一次与最后一个光标行，并在继续处理程序处理后一次。

DROP PROCEDURE IF EXISTS CleanupCompanies;
DELIMITER //
CREATE PROCEDURE CleanupCompanies()
       MODIFIES SQL DATA
       COMMENT 'Clean up demo data.'
       BEGIN
       DECLARE blnDone BOOL DEFAULT FALSE;
       DECLARE intCurrentId INTEGER UNSIGNED;
       DECLARE curCompaniesToBeCleaned CURSOR FOR
               SELECT intCompanyId FROM CompanyCleanup;
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET blnDone=TRUE;
       CREATE TEMPORARY TABLE CompanyCleanup
              ENGINE=Memory
              AS SELECT intCompanyId FROM Company INNER JOIN SiteUser ON intCreatedByUserId=intSiteUserId
                        WHERE vchLogin='DemoUser';
       OPEN curCompaniesToBeCleaned;
       REPEAT
              FETCH curCompaniesToBeCleaned INTO intCurrentId;
              DELETE IGNORE FROM Company WHERE intCompanyId=intCurrentId;
              UNTIL blnDone
       END REPEAT;
       DROP TEMPORARY TABLE CompanyCleanup;
       END//
DELIMITER ;
CALL CleanupCompanies();
DROP PROCEDURE CleanupCompanies;

GRANTS和查询日志。

查询日志是MySQL的一个优点：它包含发送到服务器的所有查询。这是一个很好的调试功能，因此默认情况下不启用。如果要在实时服务器上启用查询日志（比如，为了找出出错问题），您必须意识到这可能带来安全隐患。任何可以读取日志的人都可以轻松查找GRANT ， CREATE USER或SET PASSWORD语句。
幸运的是，您可以暂时切换注销：

SET @LogSave=@@session.sql_log_off;
SET SESSION sql_log_off=1; -- For security, do not allow the following statements to be logged.
GRANT SELECT ON YourDatabase.* TO 'YourUser'@'localhost' IDENTIFIED BY 'PasswordInPlaintext!';
GRANT SELECT ON YourDatabase.* TO 'YourUser'@'127.0.0.1' IDENTIFIED BY 'PasswordInPlaintext'; -- For SSH tunnels
SET SESSION sql_log_off=@LogSave;

或者，您可以选择在脚本中绝对不给出明文密码。例如，对于GRANT语句，您可以使用IDENTIFIED BY PASSWORD子句来使用mysql.user表中找到的哈希值。

冲洗表

在某些系统上，我注意到information_schema数据库并不总是正确反映数据库的状态。因此，我使用FLUSH TABLES命令启动我的脚本，以确保我可以信任来自information_schema的值。

服务器模式

编写必须在多个数据库实例上运行的脚本时，使用适用于所有服务器设置的SQL是非常好的。 MySQL可能以ANSI模式或“默认”模式运行，并且可以配置很多变化。
首先要看的是双引号。双引号是“MySQL默认”SQL中的字符串引号，但ANSI模式中的引用引号，因为它也是SQL标准中的标识符引号。但是，单引号总是字符串引号和反引号（`）始终是标识符引号，无论服务器设置如何。因此，如果您不需要符合标准，请使用反引号而不是双引号，最好不要对字符串使用双引号。
另一个提示是使用CONCAT()函数来连接字符串和布尔表达式中的OR运算符，作为|| 操作员可以是一个，具体取决于服务器配置。
此外，直接在函数名后面直接写入前括号，并且始终使用GROUP BY在聚合查询中选择的任何字段，而不使用聚合函数：

SELECT "Sample language ISO code: " || vchIsoCode FROM UserLanguage
       ORDER BY `PRIMARY` LIMIT 1; -- Might not work
SELECT CONCAT('Sample language ISO code: ', vchIsoCode) FROM UserLanguage
       ORDER BY `PRIMARY` LIMIT 1; -- Will work.
SELECT TRUE || FALSE; -- Might return '10' or 1, depending on server mode
SELECT TRUE OR FALSE; -- Will return 1 (=true)
SELECT COUNT (0) FROM UserLanguage; -- Might not work, depending on server mode
SELECT COUNT(0) FROM UserLanguage; -- Will work.
SELECT Street, MIN(HouseNumber) FROM Address; -- Might not work, depending on server mode
SETECT Street, MIN(HouseNumber) FROM Address GROUP BY Street; -- Will work.
-- And don't forget, comments start with minus-minus-space rather than a double minus.
SELECT 1--2; -- Might return 1 or 3
SELECT 1-- 2; -- Returns 1

Windows和lower_case_table_names

lower_case_table_names设置是使MySQL能够在大小写敏感和不区分大小写的系统上工作。但是，此设置在Windows上的默认值为最差：将其设置为1（将所有表名称甚至数据库名称都删除为小写）。这使得在Windows上开发一个在Linux或BSD上运行的数据库几乎不可能，因为大多数SHOW命令和information_schema中的表都将返回错误的名称。
所以如果你在Windows上安装MySQL，那么将这个值设置为2（不区分大小写）是很好的，通过将以下行添加到my.ini中的[mysqld]部分：

lower_case_table_names=2

在安装MySQL之后直接进行此操作，然后再定义任何数据库。你可以稍后这样做，但你会注意到很多表和数据库保持小写。

请注意，我不检查表的情况。如果脚本必须在将lower_case_table_names设置设置为“强制小写”的系统上运行，则只能使用小写名称。对于混合大小写数据库，遵循检查的ALTER TABLE命令将失败，除非您使用预准备语句来处理构建正确查询的问题。这样做会使代码比现在更难阅读。

清理例程一旦滚出来

升级例程不能完全提高您的代码的易读性。这就是为什么我在其中陈述“最后修改”的注释，所以当我确定每个安装都升级到该状态时，我可以删除它们。

字符编码

到目前为止，我已经将英文口译到我的数据库服务器，没有使用特殊字符。但是，有足够的字符可能需要输入到您的数据库中，这些字符不属于部分或英文或默认编码。您可以使用SET NAMES命令启动脚本，以通知服务器脚本中使用的字符编码，但也可以将其设置在需要的位置。您可以使用下划线在MySQL中使用编码标识符前缀，以通知用于值的编码：

-- For this to work, the encoding of the source must be utf-8:
SET @EuroSign = _utf8 '€';
-- The same character, but this statement can even be re-encoded and will still work:
SET @EuroSign = _utf8 0xE282AC;
INSERT IGNORE INTO Translation(intLanguageId,vchKeyword,txtTranslation)
       VALUES(@EnglishId, 'PriceRemark', CONCAT('All prices are given in ', @EuroSign, '.'));

如果要知道字符的十六进制值，请在数据库前端程序中使用如下所示的查询，并具有良好的编码支持：

SELECT HEX('€');

将结果用“0x”进行前缀以获取十六进制字符串表示形式。您可以使用BINARY关键字来处理您不想编码的数据：

#!/bin/bash
CONTENT=0x`hexdump -v -e '1 1 "%02X"' Delete16.png`
QUERY="INSERT IGNORE INTO Images(vchName,blbContent) VALUES('Delete', BINARY $CONTENT)"
echo $QUERY | mysql -u root -p YourDatabase

这是所有SQL

还是吗存储在数据库中的所有内容都具有SQL的形式，但这可能不是最佳形式。例如，您可以将HTML模板存储在内容管理系统的数据库中。这些模板在开发过程中作为单独的文件更为有用，因为您的程序员编辑器通常是比数据库前端更好的编辑工具。您可以使用脚本将这些模板文件“编译”为SQL插入，并将生成的文件包含在主脚本中。
我们现在非常接近使用一种“构建”脚本，它将从版本控制服务器更新源，并在必要时生成包含的脚本，并在数据库上运行生成的脚本。我在开发和测试机器以及在线服务器上都这样做。在实时服务器上，此脚本不会自动运行，因此只有在我选择时才更新该站点。
在测试机上，脚本由CRON作业自动运行，结果将发送到我们的监控系统，以及单元测试的结果。
在开发机器上，在进行一些SQL更改之后，我经常手动运行脚本（没有版本控制更新），并且在关闭之前也直接在版本控制更新之后运行脚本。
查询生成脚本可能如下所示：

#!/bin/bash
# Creates the templates insertion script.
# The template files are all in one directory and are
# named after their code, but with an extension .html
# for ease of editing.
# The contents are left-trimmed for spaces.
HERE=`dirname $0` # so we can refer to paths relative to this script
SQL_SCRIPT="$HERE/../../database/specific/templates.sql" # output
ESCAPER=$HERE/../database/mysqlescape.php
function CodeOf # filename
        {
         basename $1 | egrep -o '^[^\.]{1,}' # Everything before the first dot
        }
function CompactContentOf # text
        {
         egrep -o '[^ ].*$' < $1 | $ESCAPER
        }
echo '-- Generated file. Edit the templates, not these queries:' > $SQL_SCRIPT
for TEMPLATE in $HERE/../../documentation/templates/*.html;do
    echo "INSERT INTO Template(strCode,txtContent)" >> $SQL_SCRIPT
    echo "       VALUES('`CodeOf $TEMPLATE`', _utf8 `CompactContentOf $TEMPLATE`)" >> $SQL_SCRIPT
    echo "       ON DUPLICATE KEY UPDATE txtContent=VALUES(txtContent);" >> $SQL_SCRIPT
done

通过将生成的SQL脚本添加到“忽略”列表中，确保将生成的SQL脚本保留在版本控制系统之外。否则，你几乎肯定会花很多时间来解决冲突。上面使用的MySQL escaper可以在命令行PHP或Python中下载。

利用MySQL发展数据库