如果不存在则插入 [英] Insert if not exists

问题描述

如何在表中只有 3 行并且只更新它们?
我有 settings 表，第一次运行时什么都没有，所以我想插入 3 条记录，如下所示:

<前>身份证 |标签 |价值 |描述--------------------------1 开始 10 02 中 24 03 结束 76 0

从 PHP 脚本执行此操作后，我需要从一个查询更新此设置.我研究了 REPLACE INTO，但最终在 DB 中得到了重复的行.

这是我当前的查询:

$query_insert=" REPLACE INTO setari (`eticheta`, `valoare`, `disabled`)VALUES ('mentenanta', '".$mentenanta."', '0'),('nr_incercari_login', '".$nr_incercari_login."', '0'),('timp_restrictie_login', '".$timp_restrictie_login."', '0')";

有什么想法吗?

这是创建表语句.以防万一我遗漏了什么.

CREATE TABLE `setari` (`id` int(10) unsigned NOT NULL auto_increment，`eticheta` varchar(200) 不为空，`valoare` varchar(250) 非空，`disabled` tinyint(1) 无符号 NOT NULL 默认值 '0'，`data` 日期时间默认为 NULL，`cod` varchar(50) 默认为 NULL，PRIMARY KEY (`eticheta`,`id`,`valoare`),唯一键`id`(`eticheta`，`id`，`valoare`)) 引擎=MyISAM

解决方案

你想要的是使用 'ON DUPLICATE KEY UPDATE' 语法.通读它以获取完整的详细信息，但本质上，您需要为您的一个字段设置一个唯一键或主键，然后开始一个普通的插入查询并将该代码(以及您想要实际更新的内容)添加到最后.然后，数据库引擎将尝试添加信息，当遇到已插入的重复键时，它已经知道只用新信息更新您告诉它的所有字段.

How to have only 3 rows in the table and only update them?
I have the settings table and at first run there is nothing so I want to insert 3 records like so:

id | label | Value | desc
--------------------------
1    start    10       0
2    middle   24       0
3    end      76       0

After this from PHP script I need to update this settings from one query. I have researched REPLACE INTO but I end up with duplicate rows in DB.

Here is my current query:

$query_insert=" REPLACE INTO setari (`eticheta`, `valoare`, `disabled`) 
                VALUES ('mentenanta', '".$mentenanta."', '0'), 
                       ('nr_incercari_login', '".$nr_incercari_login."', '0'), 
                       ('timp_restrictie_login', '".$timp_restrictie_login."', '0') 
              "; 

Any ideas?

Here is the create table statement. Just so you can see in case I'm missing something.

CREATE TABLE `setari` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `eticheta` varchar(200) NOT NULL,
    `valoare` varchar(250) NOT NULL,
    `disabled` tinyint(1) unsigned NOT NULL default '0',
    `data` datetime default NULL,
    `cod` varchar(50) default NULL,
    PRIMARY KEY  (`eticheta`,`id`,`valoare`),
    UNIQUE KEY `id` (`eticheta`,`id`,`valoare`)
) ENGINE=MyISAM

解决方案

What you want is to use 'ON DUPLICATE KEY UPDATE' syntax. Read through it for the full details but, essentially you need to have a unique or primary key for one of your fields, then start a normal insert query and add that code (along with what you want to actually update) to the end. The db engine will then try to add the information and when it comes across a duplicate key already inserted, it already knows to just update all the fields you tell it to with the new information.

这篇关于如果不存在则插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！