PDO：检查数据库中的标签是否存在，然后插入
我正在使用 mysql 的 PDO 连接，我想对我用来检查 tags 是否存在的查询有一些意见在数据库上，并在没有的情况下添加它。
// the tags are allready processed in $tags array
$check_stmt = $connection->prepare ("SELECT * FROM tags WHERE tag_name = :tag_name");
$save_stmt = $connection->prepare ("INSERT INTO tags (tag_name) VALUES (:tag_name)");
foreach ($tags as $current_tag) {
$check_stmt->bindParam (':tag_name', $current_tag, PDO::PARAM_STR, 32);
$save_stmt->bindParam (':tag_name', $current_tag, PDO::PARAM_STR, 32);
$check_stmt->execute ($current_tag);
if ($check_stmt->rowCount() == 0) $save_stmt->execute ($current_tag);
}
我不熟悉数据库，所以我不确定查询是否得到了很好的预测
I'm working with PDO connection for mysql and I'd like to have some opinion on a query I use to check if tags are present on the database, and to add it in the case it isn't.
// the tags are allready processed in $tags array
$check_stmt = $connection->prepare ("SELECT * FROM tags WHERE tag_name = :tag_name");
$save_stmt = $connection->prepare ("INSERT INTO tags (tag_name) VALUES (:tag_name)");
foreach ($tags as $current_tag) {
$check_stmt->bindParam (':tag_name', $current_tag, PDO::PARAM_STR, 32);
$save_stmt->bindParam (':tag_name', $current_tag, PDO::PARAM_STR, 32);
$check_stmt->execute ($current_tag);
if ($check_stmt->rowCount() == 0) $save_stmt->execute ($current_tag);
}
I'm not skilled with databases so I'm not sure if the query is well projected
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我会稍微调整您的选择查询以进行优化：
SELECTing * 会从数据库传输更多的数据（匹配记录中的所有字段）到您的应用程序，而不是必要的。仅选择您需要的字段会更有效，在这种情况下，您看起来只是检查是否存在，因此不需要任何记录数据，因此 SELECT 1。
LIMIT 1 将查询结果限制为一条记录，而不是所有匹配的记录。查询执行速度更快，数据传输更少。
I'd adjust your selection query a bit, to optimize:
SELECTing * transmits much more data (all fields in matching records) from the db to your app than is necessary. selecting only the fields you need is much more efficient, and in this case it looks like you're just checking for existence, so you don't need any record data, therefore the SELECT 1.
The LIMIT 1 limits the query results to one record, instead of all matching ones. Quicker query execution and again less data transfer.
一些肮脏的 MySQL 特定选项包括简单地使用 REPLACE INTO（不要）或 IGNORE 关键字与 INSERT 结合使用（建议）。 INSERT IGNORE 语法比单独执行 SELECT 稍微快一些。
Some dirtier MySQL-specific options include simply using REPLACE INTO (don't) or the IGNORE keyword in combination with INSERT (suggested). The INSERT IGNORE syntax will be slightly faster than executing your SELECT separately.