将 MySQL 迁移到 PostgreSQL - 哪些 SQL 代码中不可见的功能很重要？
我们正在将 MySQL 迁移到 PostgreSQL。 我可以轻松审核整个 (REALbasic) 程序中使用的架构和 SQL 语句。 大多数 SQL 都是通过构建字符串变量组成的。
我已经知道需要将 SELECT LAST_INSERT_ID() 的使用替换为具有 UNIQUE 约束的 SERIAL 列。
在 SQL 语句中不明显可见的两者之间的差异（如果有）可能会困扰我们？ 我正在寻找关于行为的（可能是微妙的）假设，例如自动提交中的任何差异，需要添加MySQL中没有的约束等。
我正在尝试为一些相当聪明、细心的人梳理出任何陷阱，他们不是这两个数据库的专家。
这是一项单向承诺，因此如果我们通过添加新声明可以获得重大好处，我将不胜感激他们的指出。
注意：我们没有使用任何形式的参数化查询，是的，我已经指出注入攻击的问题作为代码的必要审核。
是的，出于好奇，这个决定是由 GPL 问题引发的，并不是说我们反对支付许可证费用，但不幸的是，MySQL 的唯一 REALbasic 驱动程序是 GPL。 截至 2009 年 5 月，Real Software 发布了新的社区驱动程序，该驱动程序是 GPL，并正确包含源代码。 他们承诺在不久的将来推出非 GPL Enterprise 驱动程序。
我准备相信答案可能是床底下没有隐形的怪物，但我想我会要求确定一下。
We're migrating MySQL to PostgreSQL. I can easily audit the schema and the SQL statements used throughout the (REALbasic) program. Most of the SQL is composed by building string variables.
I already know about needing to replace our use of SELECT LAST_INSERT_ID() with a SERIAL column with UNIQUE constraint.
What, if any, differences between the two which are not obviously visible in SQL statements might bite us? I'm looking for (probably subtle) assumptions about behaviour such as any differences in autocommit, need to add constraints which aren't in MySQL etc.
I'm trying to tease out any gotchas for a couple of reasonably smart, attentive guys who aren't gurus in either of the databases.
This is a one-way commitment so if there are major benefits we will get by adding new declarations I'd appreciate them pointing out.
Note: we're not using any form of parameterised queries and, yes, I've pointed out issues with injection attacks as a required audit of the code.
Yes, for the curious, this decision was prompted by GPL issues, not that we're averse to paying for licenses but, unfortunately, the sole REALbasic driver for MySQL was GPL. As of May 2009, Real Software have released a new Community driver which is GPL, and properly includes source. They have promised a non-GPL Enterprise driver in the near future.
I am prepared to believe that the answer might be there are no invisible monsters under the bed but thought I'd ask to be sure.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
从表中选择计数(*)；会很慢，因为它需要读取整个表。 如果您需要经常计算大表，则需要解决方法。 这是确保多版本并发控制所必需的。
在最新版本 (8.3) 中，没有隐式转换为文本，这意味着例如
<块引用>
选择 234，如“2%”；会抛出错误。 您需要显式强制转换，例如：
<块引用>
选择 234::text like '2%';更新实际上是删除+插入。 由于已删除行使用的空间不会立即释放，因此如果您在一个事务中更新整个表，那么您将需要双倍的空间。
Postgresql 是一个非常好的数据库，您很快就会喜欢它。 它有几个非常有用的功能，您可能会在其他数据库（甚至是商业数据库）中错过这些功能。 例如事务数据定义语言或保存点。
will be slow, as it needs to read entire table. It needs workarounds if you need to count big tables often. This is needed to ensure multiversion concurrency control.
In the latest version (8.3) there's no implicit cast to text, which means that for example
will throw error. You'll need explicit cast like:
Update is really a delete+insert. As space used by deleted rows is not immediately freed then if you update entire table in one transaction then you'll need double the space.
Postgresql is a very good database, you'll love it in no time. It has several very useful features that you'll then miss in other, even commercial databases. For example transactional data definition language or savepoints.
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
当我从 MySQL 迁移到 PostgreSQL 时，有几件事确实阻碍了我：
1）写入 MySQL 数据库的代码被破坏，并且有外键的真实数据库会阻止向数据库提供垃圾。 添加引用完整性后，准备好发现“令人惊讶的”垃圾数据。
2）MySQL对字符串的索引不区分大小写！ 如果您有用户名之类的主键，则根据 MySQL，“Coryking”和“CORYKING”是相同的。 在 PostgreSQL 上，它们是不同的。 直到有人开始注册本来应该存在于数据库中的重复用户名时，我才意识到这一点。
3) MySQL 喜欢自动向您指定为“NOT NULL”的列添加无意义的默认值。 例如，如果您指定 VARCHAR(255) NOT NULL，它将将该列定义转换为“VARCHAR(255) NOT NULL DEFAULT ''”。
4) PostgreSQL 喜欢大查询——MySQL 不喜欢。 迁移后，您将在改进数据库查询方面获得很多乐趣——也不要害羞。
When I made the migration from MySQL to PostgreSQL, several things really got in my way:
1) The code writing to the MySQL database was broken and feeding the database garbage a real database that had foreign keys would have prevented. Be prepared to find "surprise" garbage data once you add in referential integrity.
2) MySQL's indexes on strings are case insensitive! If you have a primary key on something like a username, "Coryking" and "CORYKING" are the same according to MySQL. On PostgreSQL, they are different. I wasn't aware of this until there started to be people who were registering duplicate usernames that should already be in the database.
3) MySQL likes to automagically add nonsense default values to columns you specify as "NOT NULL". For example, if you specify a VARCHAR(255) NOT NULL, it will turn that column definition into "VARCHAR(255) NOT NULL DEFAULT ''".
4) PostgreSQL likes huge queries--MySQL doesn't. You'll have a lot of fun after the migration improving your database queries--don't be shy about it either.
除非您的列类型符合 SQL92，否则您将会遇到两者之间类型名称的差异。
查询内变量修改在 postgreSQL 中不起作用，例如，这在 MySQL 中起作用，但在 postgreSQL 中不起作用（我最近没有测试过，也许现在可以工作。）
SET @a:=1
从 some_table 中选择 ID,@a:=@a+1;
我个人还认为 postgreSQL 可以更好地处理包括子选择等在内的复杂查询（大多数 MySQL 用户过去都避免这样做）。
编辑：哦，我差点忘了！ postgreSQL 存储表的方式与 MySQL 完全不同。 这也可能会影响您的备份/恢复策略。
Unless you've been SQL92-compliant with your column-typing, you will run into differences in the names of types between the two.
In-query variable modification doesn't work in postgreSQL, for instance, this will work in MySQL but not in postgreSQL (I haven't tested this recently, maybe it does work now.)
SET @a:=1
SELECT ID,@a:=@a+1 FROM some_table;
I also personally believe that postgreSQL has better handling of complex queries that include sub-selects and the like (which most MySQL users used to avoid).
Edit: Oh, and I nearly forgot! The way postgreSQL stores tables is utterly different than how MySQL does. This might affect your backup/restore strategies, too.
我不知道你是否使用PHP，但我发现addslashes对于MySQL来说效果相对较好，但在Postgres中却很快。 使用 pg_escape_string() 或更好的是准备好的语句。
I don't know if you use PHP or not but I've found that addslashes works relatively well for MySQL but bombs fast in Postgres. Either use pg_escape_string() or better yet a prepared statement.
根据所涉及的查询数量（如果您内部有人来做这件事），明智的做法是提取所有查询，根据需要调整它们，并在现有数据库的两个副本上运行它们，一个运行 mysql，另一个运行波斯格雷斯尔。 查看生成的日志并比较结果数据可能会显示一些有趣的提示。 第一步也可以通过执行单元测试、手动或基于脚本的应用程序测试来完成。
Depending on the amount of queries involved (and if you have someone inhouse to do it), it might be wise to extract all the queries, adapt them as necessary and run them on two copies of the existing DB, one running mysql and one running posgresl. Looking at the produced logs and comparing resulting datas might show some interesting hints. The first step could also be done by executing unit tests, manual or script based testing of the application.
基于WikiVS比较我刚刚发现了一些有趣的点，大多数并不是真正的陷阱，比如作为：
- Postgres 中的子查询要快得多，这并不是真正的陷阱，而是意味着可以删除一些解决方法
该网站确实引导我到了 Postgres 陷阱 列表，其中包含有关 count(*) 速度和另一个顺序扫描问题的更多详细信息：Max 和 Min 是顺序扫描。 与迄今为止发现的其他任何事情相比，这更有可能损害我们的绩效。 但是，该文章包含 Max(col) 解决方法：
Based on the WikiVS comparison I just found a number of interesting points, most not really gotchas, such as:
- subqueries are a lot faster in Postgres, this isn't really a gotcha but means some workarounds can be removed
That site did lead me to the Postgres Gotchas list, which had more details on count(*) speed and another sequential scan issue: Max and Min are sequential scans. This is much more likely to hurt our performance than anything else identified so far. However, that article includes a Max(col) workaround: