将MySQL迁移到PostgreSQL-SQL代码中不可见的哪些功能很重要? [英] Migrating MySQL to PostgreSQL - what features not visible in SQL code will be important?

问题描述

我们正在将MySQL迁移到PostgreSQL.我可以轻松审核整个(REALbasic)程序中使用的架构和SQL语句.大多数SQL是由构建字符串变量组成的.

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.

我已经知道需要用具有UNIQUE约束的SERIAL列替换对SELECT LAST_INSERT_ID()的使用.

I already know about needing to replace our use of SELECT LAST_INSERT_ID() with a SERIAL column with UNIQUE constraint.

在SQL语句中明显可见的不这两者之间的差异是什么，可能会咬我们吗?我正在寻找有关行为的(可能是微妙的)假设，例如自动提交中的任何差异，需要添加MySQL中没有的约束等.

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.

是的，出于好奇，此决定是由GPL问题引起的，并不是说我们不愿意支付许可证费用，而是不幸的是，MySQL的唯一REALbasic驱动程序是GPL.截至2009年5月，Real Software已发布了一个新的社区驱动程序，它是GPL，并且正确包含了源代码.他们承诺在不久的将来将推出非GPL Enterprise驱动程序.

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.

推荐答案

• • select count(*) from table;

    会很慢，因为它需要读取整个表.如果您需要经常计算大表，则需要变通方法.为了确保多版本并发控制.

    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.

    在最新版本(8.3)中，没有隐式转换为文本，这意味着例如

    In the latest version (8.3) there's no implicit cast to text, which means that for example

    select 234 like '2%';

    将引发错误.您需要像这样的显式强制转换:

    will throw error. You'll need explicit cast like:

    select 234::text like '2%';

  • 更新实际上是一个删除+插入.由于被删除的行占用的空间不会立即释放，因此如果您在一个事务中更新整个表，则需要两倍的空间.

  • 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是一个非常好的数据库，您会立即喜欢它.它具有几个非常有用的功能，您将在其他甚至商业数据库中错过这些功能.例如事务数据定义语言或保存点.

    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.

    这篇关于将MySQL迁移到PostgreSQL-SQL代码中不可见的哪些功能很重要?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！