I'd like to give customers a choice of the database engine, but also want to minimize my troubles of such a decision.
The engines in question are MySQL (5 or later) and SQL Server (2005 or later).
我想给客户一个数据库引擎的选择,但也希望尽量减少我这样做的麻烦。有问题的引擎是MySQL(5或更高版本)和SQL Server(2005或更高版本)。
10 个解决方案
#1
5
Google for the differences in datatypes.
谷歌对数据类型的差异。
But the schema is just a part of the picture.
但架构只是图片的一部分。
The SQL dialects are different too. Google for those differences. Then, either stick to a subset of SQL that's common to both, or build in a scheme to use somewhat different SQL in each.
SQL方言也不同。谷歌为这些差异。然后,要么坚持使用两者共有的SQL子集,要么构建一个方案以在每个SQL中使用稍微不同的SQL。
Don't wait until the end to test on the "other" db. Test on both from the start so that you don't invest too much in a dead end design direction.
不要等到最后测试“其他”数据库。从一开始就测试两者,这样你就不会在死胡同的设计方向上投入太多。
Here's a starting place:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx#EZD
and:
http://troels.arvin.dk/db/rdbms/#insert
这是一个起点:http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx#EZD和:http://troels.arvin.dk/db/rdbms/#insert
#2
4
My four main guidelines for cross-database development like this are:
我这样的跨数据库开发的四个主要指导原则是:
- Don't use spaces in names for anything
- Don't use keywords from either db as column names (ORDER, DATE etc.)
- Use the simplest column types possible (CHAR, INT). Date- and Timestamp-type column differences will probably trip you up at some point, so avoid them if possible (that's not usually realistic, I know).
- De-normalize more than you might think is appropriate. The more complicated and JOIN-y your query is, the more likely you will end up having to maintain db-specific paired versions of your queries to get everything to work acceptably on both databases. Once you're there, you're doomed.
不要在名称中使用空格
不要将db中的关键字用作列名(ORDER,DATE等)
尽可能使用最简单的列类型(CHAR,INT)。日期和时间戳类型的列差异可能会在某些时候让你失望,所以尽可能避免它们(我知道这通常不现实)。
比你想象的更适合去标准化。您的查询越复杂和JOIN-y越有可能最终需要维护特定于db的配对版本的查询,以使所有内容在两个数据库上都可以接受。一旦你在那里,你就注定要失败。
1 and 2 are really the same problem - all databases allow escaping object names (so you can have names with spaces and names like ORDER and DATE), but they usually do it differently, so this query for SQL Server:
1和2实际上是同一个问题 - 所有数据库都允许转义对象名称(因此您可以使用空格和名称,如ORDER和DATE),但它们通常采用不同的方式,因此SQL Server的此查询:
SELECT [ORDER], [Why This Name] FROM [Table From Hell]
has to be
必须
SELECT "ORDER", "Why This Name" FROM "Table From Hell"
in Oracle, and then you have two versions of each query or some uber-janky delimiter-replacement code. I usually go with the simpler approach of just not using keywords or spaces.
在Oracle中,然后您有每个查询的两个版本或一些uber-janky分隔符替换代码。我通常采用更简单的方法,不使用关键字或空格。
#3
1
This article describes some of the main differences.
本文介绍了一些主要的区别。
Differences Between MSSQL and mySQL
MSSQL和mySQL之间的差异
You probably would want to handle the differences in a Data Access Layer.
您可能希望处理数据访问层中的差异。
#4
1
Depending upon which language you use, you can insert a ORM layer in-between, such as (for instance) Doctrine PHP which should help you not have to write SQL directly. There are a number of great suggestions as far as the initial schema in other comments.
根据您使用的语言,您可以在其间插入ORM层,例如(例如)Doctrine PHP,它可以帮助您不必直接编写SQL。就其他评论中的初始模式而言,有许多很好的建议。
#5
1
Mysql provide an export functionnality that let you export your schema into SQL Server compatible format.
Mysql提供了一个导出功能,可以将模式导出为SQL Server兼容格式。
As said before, use the simpliest type possible, and do not rely on standard SQL.
如前所述,尽可能使用最简单的类型,而不依赖于标准SQL。
#6
0
One "fun" option is to not write SQL at all. Build a system that you can abstract the spec of what you want to do and then generate the schema/queries etc. I have a project where I'm building SQL code via the C pre processor.
一个“有趣”的选择是根本不写SQL。构建一个系统,您可以抽象出您想要做的规范,然后生成模式/查询等。我有一个项目,我通过C预处理器构建SQL代码。
I would not recommend either of the above unless you like playing with highly abstracted code. It tends to act as a long lever, you push really really hard on the short end, move it a tiny bit and huge things happen.
除非你喜欢使用高度抽象的代码,否则我不会推荐上述任何一种。它倾向于充当一个长杠杆,你在短端上真的非常努力,移动它一点点,并发生巨大的事情。
#7
0
If you download and use VisioModeler to capture your conceptual and/or logical design, it's pretty good at spitting out the DDL for an equivalent physical design for several RDBMSs.
如果您下载并使用VisioModeler来捕获概念和/或逻辑设计,那么很好地将DDL分配给几个RDBMS的等效物理设计。
Or there are a number of other ERD type tools that accomplish roughly the same thing. You might try DBDesigner.
或者还有许多其他ERD类型的工具可以完成大致相同的工作。您可以尝试DBDesigner。
#8
0
There are several problems I have run across in working with databases designed to use mulitple backends.
在使用旨在使用多个后端的数据库时,我遇到了几个问题。
First is that data types are different and have differnt ways of handling data. This tends to be especially tricky when handling dates and large amounts of text.
首先,数据类型不同,并且具有不同的数据处理方式。在处理日期和大量文本时,这往往特别棘手。
The next thing I have noticed is that, because they try to use ANSII standard SQL, issues arise when a database doesn't fully support the standard. An even larger problem is the standard is often not the most efficient way to get data from a particular database. Every commercial database I've ever worked with that offers multiple backends is dead slow when you get a lot of records in the tables. ANSII standard also does not offer good ways to solve of the more complex problems and so you end up with convoluted workarounds.
接下来我注意到的是,因为他们尝试使用ANSII标准SQL,所以当数据库不完全支持标准时会出现问题。更大的问题是标准通常不是从特定数据库获取数据的最有效方法。当你在表格中获得大量记录时,我曾经使用过的每个商业数据库都提供了多个后端。 ANSII标准也没有提供解决更复杂问题的好方法,因此您最终会遇到复杂的解决方法。
Another approach is to used stored procs and write one for each database backend you support but with the same names across databases. This way you can take advantage of the performance tuning available for each database and differing database structures without having to change the user interface, but it is far harder to maintain because every change has to be written for every database. However it will probably be far faster for your users than your competition, so you might be able to change a premium price for the product. You will need to charge a premium though because of the extra cost of maintenance and the extra database specialists you will need on hand to performance tune for each type of database (a mysql specialist probably won't know how to best tune a SQL Server database and vice versa)
另一种方法是使用存储过程并为您支持的每个数据库后端编写一个,但跨数据库使用相同的名称。这样,您可以利用每个数据库可用的性能调整和不同的数据库结构,而无需更改用户界面,但由于必须为每个数据库编写每个更改,因此维护起来要困难得多。但是,对于您的用户而言,它可能比竞争对手快得多,因此您可以更改产品的高价。您需要收取额外的费用,因为需要额外的维护费用以及您需要的额外数据库专家来为每种类型的数据库调整性能(mysql专家可能不知道如何最好地调整SQL Server数据库反之亦然)
An alternative to consider is that you host the data and the users access it from the web. Then you only need to design and support the one database you choose. You will need a whole pile of servers and network and dba people to do this though as you will need to maintain basically 24 hour uptime for each client and they may require you to store their data on servers that do not contain any data from competitors. Not knowing the business you are developing software for, it is hard to say if this is a viable alternative.
另一种需要考虑的方法是托管数据,用户可以从Web访问数据。然后,您只需设计和支持您选择的一个数据库。您将需要一大堆服务器,网络和dba人员来执行此操作,因为您需要为每个客户端维持基本上24小时的正常运行时间,并且他们可能要求您将数据存储在不包含竞争对手的任何数据的服务器上。不知道您正在开发软件的业务,很难说这是否是一个可行的替代方案。
The least expensive route is to require a particular backend. You may lose potential customers who don't want to buy a differnt backend though. If you go this way, I'd survey the potential customers to find databases they use right now, it might be that one is the de facto industry standard and you would lose very few potential customers by requiring it as the backend. Also if you can make the selling point that the product is significantly faster than the competition becasue you have a standard backend, you might be able to persuade them anyway. But if you go that route, you had better design a database that is blazingly fast.
最便宜的路线是需要特定的后端。您可能会失去那些不想购买不同后端的潜在客户。如果你这样做,我会调查潜在客户,找到他们现在使用的数据库,可能是一个事实上的行业标准,你可能会因为要求它作为后端而失去很少的潜在客户。此外,如果你能说出产品比竞争对手快得多的卖点,因为你有一个标准的后端,你或许可以说服他们。但是如果你走这条路,你最好设计一个速度极快的数据库。
#9
0
Design a logical database that is SQL specific but SQL dialect independent. The design should include Tables, columns, domains and constraints. constraints include, but are not limited to, primary key constraints and foreign key constraints.
设计SQL特定但SQL方言独立的逻辑数据库。设计应包括表,列,域和约束。约束包括但不限于主键约束和外键约束。
Domain specification will be tricky to define in terms that don't favor one SQL dialect over another.
根据不支持一种SQL方言而不是另一种SQL方言的术语定义域规范将是棘手的。
Create a physical design for each back end that conforms to the logical design, and uses the features of the specific back end.
为符合逻辑设计的每个后端创建物理设计,并使用特定后端的功能。
In the application, use lowest common denominator SQL wherever you can, without imposing too great a penalty in performance or code flexibility. Where necessary, code in back end specific methods. Hide these back end dependencies inside a few objects. Make most of the application DBMS agnostic.
在应用程序中,尽可能使用最小公分母SQL,而不会对性能或代码灵活性造成太大的损失。必要时,在后端特定方法中编写代码。在几个对象中隐藏这些后端依赖项。使大多数应用程序DBMS不可知。
#10
0
Datanamic's DeZign for Databases supports database-independent modeling.
Datanamic的DeZign for Databases支持独立于数据库的建模。
#1
5
Google for the differences in datatypes.
谷歌对数据类型的差异。
But the schema is just a part of the picture.
但架构只是图片的一部分。
The SQL dialects are different too. Google for those differences. Then, either stick to a subset of SQL that's common to both, or build in a scheme to use somewhat different SQL in each.
SQL方言也不同。谷歌为这些差异。然后,要么坚持使用两者共有的SQL子集,要么构建一个方案以在每个SQL中使用稍微不同的SQL。
Don't wait until the end to test on the "other" db. Test on both from the start so that you don't invest too much in a dead end design direction.
不要等到最后测试“其他”数据库。从一开始就测试两者,这样你就不会在死胡同的设计方向上投入太多。
Here's a starting place:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx#EZD
and:
http://troels.arvin.dk/db/rdbms/#insert
这是一个起点:http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx#EZD和:http://troels.arvin.dk/db/rdbms/#insert
#2
4
My four main guidelines for cross-database development like this are:
我这样的跨数据库开发的四个主要指导原则是:
- Don't use spaces in names for anything
- Don't use keywords from either db as column names (ORDER, DATE etc.)
- Use the simplest column types possible (CHAR, INT). Date- and Timestamp-type column differences will probably trip you up at some point, so avoid them if possible (that's not usually realistic, I know).
- De-normalize more than you might think is appropriate. The more complicated and JOIN-y your query is, the more likely you will end up having to maintain db-specific paired versions of your queries to get everything to work acceptably on both databases. Once you're there, you're doomed.
不要在名称中使用空格
不要将db中的关键字用作列名(ORDER,DATE等)
尽可能使用最简单的列类型(CHAR,INT)。日期和时间戳类型的列差异可能会在某些时候让你失望,所以尽可能避免它们(我知道这通常不现实)。
比你想象的更适合去标准化。您的查询越复杂和JOIN-y越有可能最终需要维护特定于db的配对版本的查询,以使所有内容在两个数据库上都可以接受。一旦你在那里,你就注定要失败。
1 and 2 are really the same problem - all databases allow escaping object names (so you can have names with spaces and names like ORDER and DATE), but they usually do it differently, so this query for SQL Server:
1和2实际上是同一个问题 - 所有数据库都允许转义对象名称(因此您可以使用空格和名称,如ORDER和DATE),但它们通常采用不同的方式,因此SQL Server的此查询:
SELECT [ORDER], [Why This Name] FROM [Table From Hell]
has to be
必须
SELECT "ORDER", "Why This Name" FROM "Table From Hell"
in Oracle, and then you have two versions of each query or some uber-janky delimiter-replacement code. I usually go with the simpler approach of just not using keywords or spaces.
在Oracle中,然后您有每个查询的两个版本或一些uber-janky分隔符替换代码。我通常采用更简单的方法,不使用关键字或空格。
#3
1
This article describes some of the main differences.
本文介绍了一些主要的区别。
Differences Between MSSQL and mySQL
MSSQL和mySQL之间的差异
You probably would want to handle the differences in a Data Access Layer.
您可能希望处理数据访问层中的差异。
#4
1
Depending upon which language you use, you can insert a ORM layer in-between, such as (for instance) Doctrine PHP which should help you not have to write SQL directly. There are a number of great suggestions as far as the initial schema in other comments.
根据您使用的语言,您可以在其间插入ORM层,例如(例如)Doctrine PHP,它可以帮助您不必直接编写SQL。就其他评论中的初始模式而言,有许多很好的建议。
#5
1
Mysql provide an export functionnality that let you export your schema into SQL Server compatible format.
Mysql提供了一个导出功能,可以将模式导出为SQL Server兼容格式。
As said before, use the simpliest type possible, and do not rely on standard SQL.
如前所述,尽可能使用最简单的类型,而不依赖于标准SQL。
#6
0
One "fun" option is to not write SQL at all. Build a system that you can abstract the spec of what you want to do and then generate the schema/queries etc. I have a project where I'm building SQL code via the C pre processor.
一个“有趣”的选择是根本不写SQL。构建一个系统,您可以抽象出您想要做的规范,然后生成模式/查询等。我有一个项目,我通过C预处理器构建SQL代码。
I would not recommend either of the above unless you like playing with highly abstracted code. It tends to act as a long lever, you push really really hard on the short end, move it a tiny bit and huge things happen.
除非你喜欢使用高度抽象的代码,否则我不会推荐上述任何一种。它倾向于充当一个长杠杆,你在短端上真的非常努力,移动它一点点,并发生巨大的事情。
#7
0
If you download and use VisioModeler to capture your conceptual and/or logical design, it's pretty good at spitting out the DDL for an equivalent physical design for several RDBMSs.
如果您下载并使用VisioModeler来捕获概念和/或逻辑设计,那么很好地将DDL分配给几个RDBMS的等效物理设计。
Or there are a number of other ERD type tools that accomplish roughly the same thing. You might try DBDesigner.
或者还有许多其他ERD类型的工具可以完成大致相同的工作。您可以尝试DBDesigner。
#8
0
There are several problems I have run across in working with databases designed to use mulitple backends.
在使用旨在使用多个后端的数据库时,我遇到了几个问题。
First is that data types are different and have differnt ways of handling data. This tends to be especially tricky when handling dates and large amounts of text.
首先,数据类型不同,并且具有不同的数据处理方式。在处理日期和大量文本时,这往往特别棘手。
The next thing I have noticed is that, because they try to use ANSII standard SQL, issues arise when a database doesn't fully support the standard. An even larger problem is the standard is often not the most efficient way to get data from a particular database. Every commercial database I've ever worked with that offers multiple backends is dead slow when you get a lot of records in the tables. ANSII standard also does not offer good ways to solve of the more complex problems and so you end up with convoluted workarounds.
接下来我注意到的是,因为他们尝试使用ANSII标准SQL,所以当数据库不完全支持标准时会出现问题。更大的问题是标准通常不是从特定数据库获取数据的最有效方法。当你在表格中获得大量记录时,我曾经使用过的每个商业数据库都提供了多个后端。 ANSII标准也没有提供解决更复杂问题的好方法,因此您最终会遇到复杂的解决方法。
Another approach is to used stored procs and write one for each database backend you support but with the same names across databases. This way you can take advantage of the performance tuning available for each database and differing database structures without having to change the user interface, but it is far harder to maintain because every change has to be written for every database. However it will probably be far faster for your users than your competition, so you might be able to change a premium price for the product. You will need to charge a premium though because of the extra cost of maintenance and the extra database specialists you will need on hand to performance tune for each type of database (a mysql specialist probably won't know how to best tune a SQL Server database and vice versa)
另一种方法是使用存储过程并为您支持的每个数据库后端编写一个,但跨数据库使用相同的名称。这样,您可以利用每个数据库可用的性能调整和不同的数据库结构,而无需更改用户界面,但由于必须为每个数据库编写每个更改,因此维护起来要困难得多。但是,对于您的用户而言,它可能比竞争对手快得多,因此您可以更改产品的高价。您需要收取额外的费用,因为需要额外的维护费用以及您需要的额外数据库专家来为每种类型的数据库调整性能(mysql专家可能不知道如何最好地调整SQL Server数据库反之亦然)
An alternative to consider is that you host the data and the users access it from the web. Then you only need to design and support the one database you choose. You will need a whole pile of servers and network and dba people to do this though as you will need to maintain basically 24 hour uptime for each client and they may require you to store their data on servers that do not contain any data from competitors. Not knowing the business you are developing software for, it is hard to say if this is a viable alternative.
另一种需要考虑的方法是托管数据,用户可以从Web访问数据。然后,您只需设计和支持您选择的一个数据库。您将需要一大堆服务器,网络和dba人员来执行此操作,因为您需要为每个客户端维持基本上24小时的正常运行时间,并且他们可能要求您将数据存储在不包含竞争对手的任何数据的服务器上。不知道您正在开发软件的业务,很难说这是否是一个可行的替代方案。
The least expensive route is to require a particular backend. You may lose potential customers who don't want to buy a differnt backend though. If you go this way, I'd survey the potential customers to find databases they use right now, it might be that one is the de facto industry standard and you would lose very few potential customers by requiring it as the backend. Also if you can make the selling point that the product is significantly faster than the competition becasue you have a standard backend, you might be able to persuade them anyway. But if you go that route, you had better design a database that is blazingly fast.
最便宜的路线是需要特定的后端。您可能会失去那些不想购买不同后端的潜在客户。如果你这样做,我会调查潜在客户,找到他们现在使用的数据库,可能是一个事实上的行业标准,你可能会因为要求它作为后端而失去很少的潜在客户。此外,如果你能说出产品比竞争对手快得多的卖点,因为你有一个标准的后端,你或许可以说服他们。但是如果你走这条路,你最好设计一个速度极快的数据库。
#9
0
Design a logical database that is SQL specific but SQL dialect independent. The design should include Tables, columns, domains and constraints. constraints include, but are not limited to, primary key constraints and foreign key constraints.
设计SQL特定但SQL方言独立的逻辑数据库。设计应包括表,列,域和约束。约束包括但不限于主键约束和外键约束。
Domain specification will be tricky to define in terms that don't favor one SQL dialect over another.
根据不支持一种SQL方言而不是另一种SQL方言的术语定义域规范将是棘手的。
Create a physical design for each back end that conforms to the logical design, and uses the features of the specific back end.
为符合逻辑设计的每个后端创建物理设计,并使用特定后端的功能。
In the application, use lowest common denominator SQL wherever you can, without imposing too great a penalty in performance or code flexibility. Where necessary, code in back end specific methods. Hide these back end dependencies inside a few objects. Make most of the application DBMS agnostic.
在应用程序中,尽可能使用最小公分母SQL,而不会对性能或代码灵活性造成太大的损失。必要时,在后端特定方法中编写代码。在几个对象中隐藏这些后端依赖项。使大多数应用程序DBMS不可知。
#10
0
Datanamic's DeZign for Databases supports database-independent modeling.
Datanamic的DeZign for Databases支持独立于数据库的建模。