从 MySQL 迁移到 PostgreSQL 时需要注意的事项

来自 PostgreSQL Wiki
跳转到导航跳转到搜索

最后更新于 2001 年 4 月 8 日

MySQL 可能对宇宙中最复杂的数据库项目很有用，并且可能可以扩展到计算每个海滩上的沙粒数量。但是，它对许多“高端”数据库功能的支持很少（值得称赞的是，它易于安装、易于管理，速度相对快，并且是一个无痛的数据库，几乎可以在您想使用的任何操作系统上都能很好地运行。）

PostgreSQL 和 MySQL 之间的差异

总的来说，PostgreSQL 努力遵循现有的数据库标准，而 MySQL 在这方面背景参差不齐。如果您来自使用 MySQL 或 Microsoft Access 的背景，一些更改可能会显得奇怪（例如，不使用双引号来引用字符串值）。

  • MySQL 使用非标准的 '#' 来开始注释行；PostgreSQL 则没有。相反，使用 '--'（双破折号），因为这是 ANSI 标准，并且这两个数据库都理解它。
  • MySQL 使用 ' 或 " 来引用值（例如 WHERE name = "John"）。这不是数据库的 ANSI 标准。PostgreSQL 只使用单引号来实现这一点（例如 WHERE name = 'John'）。双引号用于引用系统标识符；字段名、表名等（例如 WHERE "last name" = 'Smith'）。
  • MySQL 使用 `（重音符号或反引号）来引用系统标识符，这绝对是非标准的。
  • PostgreSQL 对字符串比较区分大小写。字段 "Smith" 与字段 "smith" 不同。对于许多来自 MySQL 和其他小型数据库系统（如 Microsoft Access）的用户来说，这是一个很大的变化。在 PostgreSQL 中，您可以：
    • 在查询中使用正确的大小写。（例如 WHERE lname='Smith'）
    • 使用转换函数，如 lower() 来搜索。（例如 WHERE lower(lname)='smith'）
    • 使用不区分大小写的运算符，如 ILIKE 或 ~*
  • PostgreSQL 中的数据库、表、字段和列名称不区分大小写，除非您在创建它们时在其名称周围添加了双引号，在这种情况下它们区分大小写。在 MySQL 中，表名区分大小写与否取决于您使用的操作系统。
  • PostgreSQL 和 MySQL 在处理日期以及处理日期的函数名称方面似乎最不同。
  • MySQL 使用 C 语言运算符进行逻辑运算（例如，'foo' || 'bar' 表示 'foo' OR 'bar'，'foo' && 'bar' 表示 'foo' AND 'bar'）。这对于 C 程序员来说可能有点帮助，但它以一种重要的方式违反了数据库标准和规则。PostgreSQL 遵循标准，使用 || 进行字符串连接（'foo' || 'bar' = 'foobar'）。
  • 这两个数据库之间还存在其他差异，例如查找当前用户的函数名称。MySQL 有一个工具，Crash-Me，可以用来找出这些差异。（表面上，Crash-Me 是一个用于比较数据库的工具；然而，它往往会严重淡化 MySQL 的不足之处，并且在它列出的内容方面并不客观：拥有过程语言（这对许多用户来说是一个非常重要的功能！）的想法被贬低到文档底部第五行的单行，而 MySQL 允许您使用 || 来表示逻辑或（绝对是非标准的），则列在它之前，作为一个功能。请注意它的解释。）

更大的图景

重要的东西（至少对我来说）不仅仅是“如何在 PostgreSQL 中做这个 MySQL 事情”，而是“是否有更好的思考方式，MySQL 甚至不支持？”

例如

想象一下，您正在为人力资源构建一个动态网站。您想列出所有当前高级员工的姓名、一些有关他们的信息以及他们目标的列表。

使用 MySQL，您将执行类似的操作

（这是通用的伪代码，它可以轻松地转换为 PHP、Zope、EmbPerl 等）

 <in sql="SELECT staffid, firstname, lastname FROM Staff WHERE datefired ISNULL and seniorstaff = TRUE">
     <h1>$firstname $lastname</h1>
       <ul>
           <in sql="SELECT goalinfo FROM Goals WHERE staffid=$staffid">
           <li>$goalinfo
           </in>
       </ul>
 </in>

这很棒，而且运行良好。您可以轻松地将它转换为 PostgreSQL。

但是，您是否想这样做？PostgreSQL 有很多 MySQL 没有的功能，例如

例如，与其在 Web 前端编码 is-not-fired 和 is-senior-staff 的逻辑，在 PostgreSQL 中，我将为我们要显示目标的所有员工创建一个视图

CREATE VIEW staff_having_goals AS
SELECT staffid, firstname || lastname as fullname
FROM Staff
WHERE datefired ISNULL and seniorstaff = TRUE
ORDER BY lastname, firstname

现在，我的 Web 编程不需要担心底层问题。想象一下，如果同一人员和目标列表在您的网站上出现几十次——我已经从将其分散在许多地方转移到将其封装在一个地方。

PostgreSQL 还允许使用过程语言（perl、tcl、python 以及类似 Oracle 的 PL/pgSQL）。这些允许您在数据库中创建函数（甚至非系统管理员也可以使用它们，因为函数符合 PostgreSQL 安全模型）。

（是的，MySQL 有用户函数，据我上次检查，这些函数必须用 C 语言编写，并链接到数据库中。这是一个不错的功能，当然，但与拥有不使用 root 权限即可使用的、高级的过程语言截然不同！）

我们可以使用这些过程语言来创建列表，处理数据库事件（如果在这里添加了记录，则自动跟踪在那里，等等。您可能有一个函数可以根据员工的薪水计算其每小时的报酬，我认为这 *应该* 是一个数据库函数，而不是为每个不同的 Web 项目或前端项目编码的函数。）

PostgreSQL 还支持事务，它可以消除一些繁琐的如果发生错误则回滚所有数据库工作的代码。（值得称赞的是，MySQL 在他们的新 MaxSQL 中有事务。）

此外，PostgreSQL 支持许多 MySQL 不支持的标准 SQL 部分，例如子查询、联合、交集等等。虽然您通常可以使用更多 SQL 或前端中的更多逻辑来绕过这些问题，但最好的（最快、最可移植、最抽象的）解决方案是将这种思考融入您的查询编写和数据库设计中。

所以

处理方式不同的内容相当少，并且通常无需太多痛苦就能处理。尤其是因为您可以轻松创建模仿 MySQL 中任何函数的 PostgreSQL 用户函数。

真正的教训是了解 PostgreSQL 的哪些功能，并弄清楚 *为什么* 要使用它们！

我建议从上面的五个功能（视图、过程语言、触发器、可定制聚合、事务）开始，并确保您准确了解它们是什么，如何使用它们，以及它们有多棒。

我希望这能有所帮助。我从使用 MySQL 转移到了 PostgreSQL，在第一次使用它后的几个月里，我一直认为它只是一个更大的、更复杂的数据库，它做的事情与 MySQL 相同。我花了一些时间才真正意识到“其他”功能有多棒。

祝你好运！

Joel Burton 信息系统支持中心主任 华盛顿州

检索自 "https://wiki.postgresql.ac.cn/index.php?title=Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL&oldid=20424"