使用 PHP 的 PDO 接口切换数据库引擎时处理 MySQL 反引号
- 我目前正在开发一个 PHP 应用程序，该应用程序使用 MySQL 数据库作为后端。
- 我的所有查询都包含反引号来转义字段名称。这样我就可以在查询中包含“密码”等字段，而不会引起问题（请参阅示例）
- 我知道反引号在关系数据库引擎之间并不通用（例如，SQLite 使用双引号）
- 我的所有查询 我的问题是：如果我想切换数据库引擎，比如从 MySQL切换
到 SQLite，我需要做什么来处理所有查询中的反引号？我真的不想检查所有代码并更改/删除反引号。有什么建议吗？我做错了什么或者不在最佳实践的范围内吗？
示例查询：
SELECT
`username`,
`password`,
`email_address`
FROM
`users`
WHERE
`id` = '1'
- I'm currently working on a PHP application that uses a MySQL database for its backend
- All of my queries contain backticks to escape the field names. This is so I can have fields like "password" in a query without causing issues (see example)
- I know that backticks are not universal between relational-database engines (SQLite uses a double-quote, for example)
- All of the queries in my php application are executed using PHP's PDO interface
My question is this: If I want to switch database engines, say from MySQL to SQLite, what do I need to do to handle the backticks in all of my queries? I really don't want to have to go through all of my code and change / remove the backticks. Any suggestions? Am I doing something wrong or not within the boundaries of best practices?
Sample Query:
SELECT
`username`,
`password`,
`email_address`
FROM
`users`
WHERE
`id` = '1'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
实际上，
password实际上并不需要引号...它甚至不是保留字：http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html恕我直言，您可以采取的最佳方法是：
无论如何，切换到另一个数据库引擎是一回事；这是一回事。构建一个独立于数据库的应用程序是一个完全不同的问题。
Actually,
passworddoes not really need to be quoted... It's not even a reserved word: http://dev.mysql.com/doc/refman/5.1/en/reserved-words.htmlIMHO, the best approach you can take is:
Whatever, switching to another DB engine is one thing; building a DB-independent app is a enterely different issue.
不要使用保留字，并且在不使用反引号时不会遇到麻烦。去掉所有反引号，这不是 SQL 标准，所有其他数据库都会有问题。标准中使用双引号，大多数数据库都支持它们。但再次强调，不要使用保留词，而且你也不需要它们。
配置 MySQL 服务器（-connection）以使用 ANSI-QUOTES 和 MySQL 也会像它最初应该做的那样对待双引号：作为标识符
Don't use reserved words and you don't get into trouble when you don't use backticks. Get rid of all backticks, it's not SQL Standard, all other databases will have problems with them. Double quotes are used in the standard, most databases support them. But again, don't use reseved words and you don't need them.
Configure your MySQL-server (-connection) to use ANSI-QUOTES and MySQL will also treat double quotes as it should have done in the first place: as an identifier
实际上，SQLite 与 MySQL 反引号引用风格兼容。
然而，相反的情况只有当您遵循@Frank 建议。
Actually, SQLite is compatible with MySQL backtick quoting style.
The inverse however, is only true if you follow @Frank advice.