编写用于部署的 SQL 脚本的最佳实践
我想知道编写 SQL 脚本来设置用于生产和/或开发的数据库的最佳实践是什么，例如：
- 我应该包含 CREATE DATABASE 语句吗？
- 我应该在同一脚本中为数据库创建用户吗？
- 在执行脚本主体之前禁用 FK 检查是否正确？
- 我可以在交易中包含洞脚本吗？
- 为每个数据库生成 1 个脚本比为所有数据库生成一个脚本更好吗？
谢谢！
I was wondering what are the best practices in order to write SQL scripts to set up databases for production and/or development, for instance:
- Should I include the CREATE DATABASE statement?
- Should I create users for the database in the same script?
- Is correct to disable FK check before executing the body of the script?
- May I include the hole script in a transaction?
- Is better to generate 1 script per database than one script for all of them?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您的问题很难回答，因为它取决于您想要实现的目标中使用脚本的方式。您也没有说明您正在使用哪个数据库服务器，因为提供的工具可以使某些任务变得更容易。
按顺序列出您的观点，这里有一些建议，这些建议可能与其他人非常不同:)
陈述？
您正在考虑使用什么替代方案？如果您的问题是是否应该将 CREATE DATABASE 语句放在与表创建相同的脚本中，这取决于它。开发数据库时，我使用单独的创建数据库脚本，因为我有一个脚本来删除所有对象，因此我不需要再次创建数据库。
我不会，只是因为用户很可能会改变，但你的架构却没有。不妨在较小的脚本中管理这些更改。
如果您导入数据是为了尝试恢复数据库，那么如果您使用自动增量 ID 并希望保留相同的值，则可能必须这样做。此外，您最终可能会“无序”导入表并且不希望执行检查。
是的，您可以，但这又取决于您正在运行的脚本的类型。如果您在重建数据库后导入数据，那么整个导入应该成功或失败。但是，在导入过程中您的交易文件将会很大。
同样，出于维护目的，最好将它们分开。
The problem with your question is is hard to answer as it depends on the way the scripts are used in what you are trying to achieve. you also don't say which DB server you are using as there are tools provided which can make some tasks easier.
Taking your points in order, here are some suggestions, which will probably be very different to everyone elses :)
statement?
What alternative are you thinking of using? If your question is should you put the
CREATE DATABASEstatement in the same script as the table creation it depends. When developing DB I use a separate create DB script as I have a script to drop all objects and so I don't need to create the database again.I wouldn't, simply because the users may well change but your schema has not. Might as well manage those changes in a smaller script.
If you are importing the data in an attempt to recover the database then you may well have to if you are using auto increment IDs and want to keep the same values. Also you may end up importing the tables "out of order" an not want checks performed.
Yes, you can, but again it depends on the type of script you are running. If you are importing data after rebuilding a db then the whole import should work or fail. However, your transaction file is going to be huge during the import.
Again, for maintenance purposes it's probably better to keep them separate.
这可能取决于什么类型的数据库以及如何使用和部署它。我正在开发一个部署在许多不同客户站点的 n 层标准应用程序。
我没有在脚本中添加 CREATE DATABASE 语句。创建数据库是安装脚本的一部分，它允许用户选择服务器、数据库名称和排序规则
我对客户站点上的用户一无所知，因此我不添加创建用户语句，这也是唯一的用户需要访问数据库的是执行中间轮胎应用程序的用户。
我不禁用 FK 检查。我需要它们来保护数据库的一致性，即使是我编写了主体脚本。我使用 FK 来捕获我的错误。
我没有将整个脚本包含在一个事务中。我要求用户在运行任何数据库升级脚本之前对数据库进行备份。对于创建新数据库来说，没有什么需要保护的，因此不需要在事务中运行。对于升级，有时会对数据库进行大量更改。几年前，我们在大约 250 个表中从 varchar 转换为 nvarchar。这不是您希望在一笔事务中执行的操作。
我建议您为每个数据库生成一个脚本，并单独对脚本进行版本控制。
This probably depends what kind of database and how it is used and deployed. I am developing a n-tier standard application that is deployed at many different customer sites.
I do not add a CREATE DATABASE statement in the script. Creating the the database is a part of the installation script which allows the user to choose server, database name and collation
I have no knowledge about the users at my customers sites so I don't add create users statements also the only user that needs access to the database is the user executing the middle tire application.
I do not disable FK checks. I need them to protect the consistency of the database, even if it is I who wrote the body scripts. I use FK to capture my errors.
I do not include the entire script in one transaction. I require from the users to take a backup of the db before they run any db upgrade scripts. For creating of a new database there is nothing to protect so running in a transaction is unnecessary. For upgrades there are sometimes extensive changes to the db. A couple of years ago we switched from varchar to nvarchar in about 250 tables. Not something you would like to do in one transaction.
I would recommend you to generate one script per database and version control the scripts separately.
直接回答，请询问您是否需要扩展任何一点
通常我会包括它，因为您正在创建并拥有数据库。
这也是一个好主意，特别是如果您的应用程序使用特定用户。
如果脚本包含数据填充，那么它有助于禁用它，以便顺序不太重要，否则您可以进入复杂的脚本来插入（没有 fk 链接）、创建 fk 记录、更新 fk 列。
这通常不是一个好主意。特别是如果包含数据量，因为交易可能会变得非常难以处理。由于您正在创建数据库，因此如果出现问题，只需将其删除并重新开始即可。
我的建议是每个数据库一个，这样它们就可以被隔离并且在需要时更容易排除故障。
Direct answers, please ask if you need to expand on any point
Normally I would include it since you are creating and owning the database.
This is also a good idea, especially if your application uses specific users.
If the script includes data population, then it helps to disable it so that the order is not too important, otherwise you can get into complex scripts to insert (without fk link), create fk record, update fk column.
This is normally not a good idea. Especially if data population is included as the transaction can become quite unwieldy large. Since you are creating the database, just drop it and start again if something goes awry.
One per database is my recommendation so that they are isolated and easier to troubleshoot if the need arises.
出于开发目的，最好为每个数据库对象创建一个脚本（每个表、存储过程等一个脚本）。如果您以这种方式将它们签入源代码控制系统，那么开发人员就可以签出各个对象，并且您可以轻松跟踪版本并了解更改的内容和时间。
部署时，您可能希望将每个版本的更改合并到一个脚本中。 Red Gate SQL 比较或 Visual Studio Team System 等工具将帮助您做到这一点。
For development purposes it's a good idea to create one script per database object (one script for each table, stored procedure, etc). If you check them into your source control system that way then developers can check out individual objects and you can easily keep track of versions and know what changed and when.
When you deploy you may want to combine the changes for each release into one single script. Tools like Red Gate SQL compare or Visual Studio Team System will help you do that.
这取决于您的 DBMS 和客户。
在 Oracle 环境中，您可能永远不会被允许做这样的事情（主要是因为在 Oracle 世界中，“数据库”与 PostgreSQL 或 MySQL 世界中的“数据库”完全不同）。
有时，客户的 DBA 不允许您创建数据库（或模式或用户 - 取决于所使用的 DBMS）。因此，您需要向 DBA 提供该信息，以便他/她为您的脚本准备环境。
这完全取决于您正在使用的 DBMS。
某些 DBMS 不支持事务性 DDL，并且在执行 DDL 语句时会隐式提交任何事务，因此您需要考虑安装脚本的顺序。
为了用数据填充表，我肯定会尝试在单个事务中执行此操作，但这又取决于您的 DBMS。
如果您仅提交一次或很少提交（Oracle 和 PostgreSQL 属于此类），某些 DBMS 会更快，但如果您更频繁地提交，则会变慢。
其他 DBMS 可以更好地处理较小但较多的事务，并且如果事务变得太大，速度就会变慢（SQL Server 和 MySQL 往往会陷入这个方向）
That depends on your DBMS and your customer.
In an Oracle environment you will probably never be allowed to do such a thing (mainly because in the Oracle world a "database" is something completely different than e.g. in the PostgreSQL or MySQL world).
Sometimes the customer will have a DBA that won't let you create databases (or schemas or users - depending on the DBMS in use). So you will need to supply that information to the DBA in order for him/her to prepare the environment for your script.
That totally depends on the DBMS that you are using.
Some DBMS don't support transactional DDL and will implicitely commit any transaction when you execute a DDL statement, so you need to consider the order of your installation script.
For populating the tables with data I would definitely try to do that in a single transaction, but again this depends on your DBMS.
Some DBMS are faster if you commit only once or very seldomly (Oracle and PostgreSQL fall into this category) but will slow down if you commit more often.
Other DBMS handle smaller but more transactions better and will slow down if the transactions get too big (SQL Server and MySQL tend to fall into that direction)
无论是首次设置还是推送新版本，最佳实践都会有很大差异。第一次设置是的，您需要创建数据库并创建表脚本。对于新版本，您只需编写与先前版本相比的更改的脚本，因此不需要创建数据库，也不需要创建表，除非它是新表。现在您需要 alter table 语句，因为您不想丢失现有数据。我通常会使用 drop 和 create 语句来编写存储过程、函数和视图，因为删除这些 pbject 通常不会影响底层数据。
我发现最好使用存储在版本下的源代码管理中的脚本创建所有数据库更改。因此，如果客户端是新的，您可以运行创建版本 1.0 脚本，然后按顺序应用所有其他版本。如果客户端刚刚从版本 1.2 升级到版本 1.3，则您只需运行版本 1.3 源控制存储库中的脚本。这还包括用于填充或添加记录到查找表的脚本。
对于事务，您可能希望将它们分成几个块，以免产品数据库锁定在一个事务中。
如果需要，我们还编写反转脚本以返回到旧版本。如果您的部分更改导致产品出现意外问题（通常是性能问题），这会让事情变得更轻松。
The best practices will differ considerably on whether it is the first time set-up or a new version being pushed. For the first time set-up yes you need create database and create table scripts. For a new version, you need to script only the changes from the previous version, so no create database and no create table unless it is a new table. Now you need alter table statements becasue you don't want to lose the existing data. I do usually write stored procs, functions and views with a drop and create statment as dropping those pbjects doesn't generally affect the underlying data.
I find it best to create all database changes with scripts that are stored in source control under the version. So if a client is new, you run the create version 1.0 scripts, then apply all the other versions in order. If a client is just upgrading from version 1.2 to version 1.3, then you run just the scripts in version 1.3 source control repository. This would also include scripts to populate or add records to lookup tables.
For transactions you may want to break them up into several chunks not to leave a prod database locked in one transaction.
We also write reversal scripts to return to the old version if need be. This makes life easier if you have a part of a change that causes unanticipated problems on prod (usually performance issues).