MySQL에서 '존재하지 않는 경우 삽입'하는 방법은 무엇입니까?

MySQL에서 '존재하지 않는 경우 삽입'하는 방법은 무엇입니까?

---

인터넷 검색으로 시작하여 뮤텍스 테이블에 대해 설명 하는 이 기사 를 찾았습니다 .

~ 1400 만 개의 레코드가있는 테이블이 있습니다. 동일한 형식으로 더 많은 데이터를 추가하려면 쿼리 쌍을 사용하지 않고 삽입하려는 레코드가 이미 존재하지 않는지 확인하는 방법이 있습니까 (즉, 검사 할 쿼리 하나와 삽입 할 쿼리 하나가 빈)?

unique필드에 대한 제약 insert이 이미 존재하는 경우 실패를 보장 합니까?

그것은과 그 보인다 단지 내가 PHP, 스크립트 croaks를 통해 삽입을 실행할 때, 제약.

---

사용하다 INSERT IGNORE INTO table

http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html 참조

INSERT … ON DUPLICATE KEY UPDATE구문 도 있으며 dev.mysql.com에서 설명을 찾을 수 있습니다.

---

Google의 웹 캐시에 따라 bogdan.org.ua에서 게시 :

2007 년 10 월 18 일

시작하려면 : 최신 MySQL에서는 제목에 표시된 구문이 불가능합니다. 그러나 기존 기능을 사용하여 예상되는 작업을 수행하는 몇 가지 매우 쉬운 방법이 있습니다.

세 가지 가능한 솔루션이 있습니다. INSERT IGNORE, REPLACE 또는 INSERT… ON DUPLICATE KEY UPDATE 사용.

테이블이 있다고 상상해보십시오.

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

이제 Ensembl에서 스크립트 메타 데이터를 가져 오는 자동 파이프 라인이 있고 다양한 이유로 인해 파이프 라인이 실행 단계에서 중단 될 수 있다고 상상해보십시오. 따라서 1) 파이프 라인의 반복 실행은 데이터베이스를 파괴하지 않으며 2) 반복 실행은 '중복 기본 키'오류로 인해 죽지 않습니다.

방법 1 : REPLACE 사용

매우 간단합니다.

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

레코드가 있으면 덮어 씁니다. 아직 존재하지 않는 경우 생성됩니다. 그러나이 방법을 사용하는 것은 우리의 경우에 효율적이지 않습니다. 기존 레코드를 덮어 쓸 필요가 없습니다. 그냥 건너 뛰는 것이 좋습니다.

방법 2 : INSERT IGNORE 사용 또한 매우 간단합니다.

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

여기서 'ensembl_transcript_id'가 이미 데이터베이스에있는 경우 자동으로 건너 뜁니다 (무시 됨). (더 정확하게 말하면 다음은 MySQL 참조 설명서의 인용문입니다.“IGNORE 키워드를 사용하면 INSERT 문을 실행하는 동안 발생하는 오류가 대신 경고로 처리됩니다. 예를 들어 IGNORE가 없으면 기존 UNIQUE 인덱스를 복제하는 행 또는 테이블의 PRIMARY KEY 값으로 인해 중복 키 오류가 발생하고 문이 중단됩니다.”.) 레코드가 아직 존재하지 않으면 생성됩니다.

이 두 번째 방법에는 다른 문제가 발생할 경우 쿼리를 중단하지 않는 등 몇 가지 잠재적 인 약점이 있습니다 (매뉴얼 참조). 따라서 이전에 IGNORE 키워드없이 테스트 한 경우 사용해야합니다.

There is one more option: to use INSERT … ON DUPLICATE KEY UPDATE syntax, and in the UPDATE part just do nothing do some meaningless (empty) operation, like calculating 0+0 (Geoffray suggests doing the id=id assignment for the MySQL optimization engine to ignore this operation). Advantage of this method is that it only ignores duplicate key events, and still aborts on other errors.

As a final notice: this post was inspired by Xaprb. I’d also advise to consult his other post on writing flexible SQL queries.

---

Solution:

INSERT INTO `table` (`value1`, `value2`) 
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1) 

Explanation:

The innermost query

SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1

used as the WHERE NOT EXISTS-condition detects if there already exists a row with the data to be inserted. After one row of this kind is found, the query may stop, hence the LIMIT 1 (micro-optimization, may be omitted).

The intermediate query

SELECT 'stuff for value1', 'stuff for value2' FROM DUAL

represents the values to be inserted. DUAL refers to a special one row, one column table present by default in all Oracle databases (see https://en.wikipedia.org/wiki/DUAL_table). On a MySQL-Server version 5.7.26 I got a valid query when omitting FROM DUAL, but older versions (like 5.5.60) seem to require the FROM information. By using WHERE NOT EXISTS the intermediate query returns an empty result set if the innermost query found matching data.

The outer query

INSERT INTO `table` (`value1`, `value2`) 

inserts the data, if any is returned by the intermediate query.

---

on duplicate key update, or insert ignore can be viable solutions with MySQL.

---

Example of on duplicate key update update based on mysql.com

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

---

Example of insert ignore based on mysql.com

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

---

Any simple constraint should do the job, if an exception is acceptable. Examples :

• primary key if not surrogate
• unique constraint on a column
• multi-column unique constraint

Sorry is this seems deceptively simple. I know it looks bad confronted to the link you share with us. ;-(

But I neverleless give this answer, because it seem to fill your need. (If not, it may trigger your updating your requirements, which would be "a Good Thing"(TM) also).

Edited: If an insert would break the database unique constraint, an exception is throw at the database level, relayed by the driver. It will certainly stop your script, with a failure. It must be possible in PHP to adress that case ...

---

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

If the record exists, it will be overwritten; if it does not yet exist, it will be created.

---

Here is a PHP function that will insert a row only if all the specified columns values don't already exist in the table.

• If one of the columns differ, the row will be added.

• If the table is empty, the row will be added.

• If a row exists where all the specified columns have the specified values, the row won't be added.

  function insert_unique($table, $vars)
  {
    if (count($vars)) {
      $table = mysql_real_escape_string($table);
      $vars = array_map('mysql_real_escape_string', $vars);
  
      $req = "INSERT INTO `$table` (`". join('`, `', array_keys($vars)) ."`) ";
      $req .= "SELECT '". join("', '", $vars) ."' FROM DUAL ";
      $req .= "WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE ";
  
      foreach ($vars AS $col => $val)
        $req .= "`$col`='$val' AND ";
  
      $req = substr($req, 0, -5) . ") LIMIT 1";
  
      $res = mysql_query($req) OR die();
      return mysql_insert_id();
    }
  
    return False;
  }
  

Example usage :

<?php
insert_unique('mytable', array(
  'mycolumn1' => 'myvalue1',
  'mycolumn2' => 'myvalue2',
  'mycolumn3' => 'myvalue3'
  )
);
?>

---

Try the following:

IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
  UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
  INSERT INTO beta (name) VALUES ('John')
  INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END

---

There are several answers that cover how to solve this if you have a UNIQUE index that you can check against with ON DUPLICATE KEY or INSERT IGNORE. That is not always the case, and as UNIQUE has a length constraint (1000 bytes) you might not be able to change that. For example, I had to work with metadata in WordPress (wp_postmeta).

I finally solved it with two queries:

UPDATE wp_postmeta SET meta_value = ? WHERE meta_key = ? AND post_id = ?;
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT DISTINCT ?, ?, ? FROM wp_postmeta WHERE NOT EXISTS(SELECT * FROM wp_postmeta WHERE meta_key = ? AND post_id = ?);

Query 1 is a regular UPDATE query with no effect when the dataset in question is not there. Query 2 is an INSERT which depends on a NOT EXISTS, i.e. the INSERT is only executed when the dataset doesn't exist.

---

Try:

// Check if exist cod = 56789
include "database.php";

$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
$countrows = mysql_num_rows($querycheck);
if($countrows == '1')
{
  // Exist 
}
else
{
 // .... Not exist
}

Or you can do:

// Check if exist cod = 56789
include "database.php";

$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
$countrows = mysql_num_rows($querycheck);
while($result = mysql_fetch_array($querycheck))
{
    $xxx = $result['xxx'];
    if($xxx == '56789')
    {
      // Exist
    }
    else
    {
      // Not exist
    }
}

This method is fast and easy. For improve the speed of the query in your big table INDEX columns 'xxx' ( In my example ).

---

Something worth noting is that INSERT IGNORE will still increment the primary key whether the statement was a success or not just like a normal INSERT would.

This will cause gaps in your primary keys that might make a programmer mentally unstable. Or if your application is poorly designed and depends on perfect incremental primary keys, it might become a headache.

Look into innodb_autoinc_lock_mode = 0 (server setting, and comes with a slight performance hit), or use a SELECT first to make sure your query will not fail (which also comes with a performance hit and extra code).

참고URL : https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql