php 检查记录是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2848904/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if Record Exists
提问by user329394
I found this on php.netto check if file exists.
I want to check if record exists first, then do update, otherwise do an insert.
我想先检查记录是否存在，然后进行更新，否则进行插入。
if(record exist) {
update query}
else
{ insert query}
I know how to update or insert, but don't know how to check if a record exists first. How is it done?
我知道如何更新或插入，但不知道如何首先检查记录是否存在。它是如何完成的？
回答by Dominic Rodger
If you know how to do a SQL SELECT, then do that:
如果您知道如何执行 SQL SELECT，请执行以下操作：
$result = mysql_query("SELECT * FROM table1 WHERE something");
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
// do something
}
else {
// do something else
}
Better yet, don't do this in PHP, use INSERT ... ON DUPLICATE KEY UPDATE.
更好的是，不要在 PHP 中这样做，使用INSERT ... ON DUPLICATE KEY UPDATE.
回答by Rich Adams
You don't need to do this in PHP, you can do it directly in the SQL query for your database (I'm assuming you're using a database for the records, since that's what it sounds like from your question, and I'll assume MySQL since that's often used along with PHP).
您不需要在 PHP 中执行此操作，您可以直接在您的数据库的 SQL 查询中执行此操作（我假设您正在使用数据库作为记录，因为从您的问题中听起来就是这样，我将假设 MySQL，因为它经常与 PHP 一起使用）。
INSERT INTO ... ON DUPLICATE KEY UPDATE;
This would insert a new row if it doesn't already exist, or update the current one if it does, assuming your tables have primary keys.
如果它不存在，这将插入一个新行，如果存在则更新当前行，假设您的表有主键。
See the MySQL Manualfor more info on this.
有关这方面的更多信息，请参阅MySQL 手册。
The other option is to just do a SELECT COUNT(1) FROM myTable WHERE ...query first, and then only do an insert if the result is 0, otherwise do an update.
另一种选择是先进行SELECT COUNT(1) FROM myTable WHERE ...查询，然后仅在结果为 0 时进行插入，否则进行更新。
回答by phogl
I would recommend Dominic Rodger's solution, but with a little change to make it faster. You should select a single value and not more than one row.
我会推荐 Dominic Rodger 的解决方案，但稍作改动以使其更快。您应该选择一个值而不是多于一行。
$result = mysql_query("SELECT key FROM table1 WHERE something LIMIT 1");
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
// do something
}
else {
// do something else
}
If your record already exists, you'll get a result, wich is more than 0 results, so it works, but potentially with less traffic from your SQL-Server.
如果您的记录已经存在，您将得到一个结果，即超过 0 个结果，因此它可以工作，但可能来自您的 SQL-Server 的流量较少。
回答by thegrunt
$username = $_POST["user"];
$query = mysql_query("SELECT * FROM users WHERE username='$username'");
if(mysql_num_rows($query) != 0)
{
echo "Username already exists";
}
else
{
//proceed with code here
}
回答by karim79
Count records matching your criteria?
计算符合您条件的记录？
select count(*) from foo where id = 5
if($count > 0) {
// record exists
...
}
回答by luvieere
You could do a select for that record and inspect the result, or you could try an update and see if there was an error. If there was, then there was nothing to update, so do an insert.
您可以选择该记录并检查结果，或者您可以尝试更新并查看是否有错误。如果有，那么就没有什么要更新的，所以做一个插入。
回答by user982853
Strange no one has mentioned using REPLACE?
奇怪没有人提到使用REPLACE？
回答by Elad
You can set the specific columns in your database as primary key and then the insert will success only if you don't have the record already. In this way, you don't event need to check if record exists.
您可以将数据库中的特定列设置为主键，然后只有在您没有记录时插入才会成功。这样，您无需检查记录是否存在。
回答by Nicole Bartolini
I've had the same problem and I solved using REPLACE INTO, a MySQL extension working this way:
我遇到了同样的问题，我使用 REPLACE INTO 解决了这个问题，这是一个以这种方式工作的 MySQL 扩展：
- If the record which you want to insert does not exist, the MySQL REPLACE inserts a new record.
- If the record which you want to insert already exists, MySQL REPLACE deletesthe old record first and then insert a new record.
- 如果要插入的记录不存在，MySQL REPLACE 会插入一条新记录。
- 如果要插入的记录已经存在，MySQL REPLACE 先删除旧记录，再插入新记录。
I found this method useful when I don't need to remember the old values of the record. For example, if you want to increase a value this method isn't a great way 'cause delete the old record and the old values as well.
当我不需要记住记录的旧值时，我发现这种方法很有用。例如，如果你想增加一个值，这个方法不是一个好方法，因为删除旧记录和旧值。
Remember also you need to have both INSERT and DELETE privileges to use REPLACE.
还请记住，您需要同时拥有 INSERT 和 DELETE 权限才能使用 REPLACE。
Here's and example based on my code:
这是基于我的代码的示例：
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "REPLACE INTO table_name (name, age, current_date)
VALUES ('" . $name . "', $age, '" . date('Y-m-d') . "')";
if ($conn->query($sql) === FALSE) {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
