MySQL에서 자동 증가 필드를 사용하여 행을 복사하고 동일한 테이블에 삽입하는 방법은 무엇입니까?

MySQL에서 자동 증가 필드를 사용하여 행을 복사하고 동일한 테이블에 삽입하는 방법은 무엇입니까?

---

테이블 예가 있습니다. 내가하려고하는 것은 자동 증가 열 ID가 1 인 행을 복사하고 행과 열 ID = 2가있는 동일한 테이블에 데이터를 삽입하는 것입니다.

MySql 사용 단일 쿼리로 어떻게 할 수 있습니까?

---

사용 INSERT ... SELECT:

insert into your_table (c1, c2, ...)
select c1, c2, ...
from your_table
where id = 1

를 c1, c2, ...제외한 모든 열은 어디에 있습니까 id? id를 2 로 명시 적으로 삽입하려면 INSERT 열 목록과 SELECT에 포함하십시오.

insert into your_table (id, c1, c2, ...)
select 2, c1, c2, ...
from your_table
where id = 1

id물론 두 번째 경우에는 2 의 가능한 복제본 을 처리해야합니다.

---

IMO는 가장 좋은 것은 SQL 문을 사용하여 해당 행을 복사하는 동시에 동시에 필요하고 변경하려는 열만 참조하는 것 같습니다.

CREATE TEMPORARY TABLE temp_table ENGINE=MEMORY

SELECT * FROM your_table WHERE id=1;
UPDATE temp_table SET id=NULL; /* Update other values at will. */

INSERT INTO your_table SELECT * FROM temp_table;
DROP TABLE temp_table;

av8n.com-SQL 레코드 복제 방법 도 참조하십시오.

혜택:

• SQL 문 2는 복제 프로세스 중에 변경해야하는 필드 만 언급합니다. 그들은 다른 분야에 대해 알지 못하거나 걱정하지 않습니다. 다른 필드는 변경없이 그대로갑니다. 이는 SQL 문 작성, 읽기, 유지 보수 및 확장 성을 향상시킵니다.
• 일반적인 MySQL 문만 사용됩니다. 다른 도구 나 프로그래밍 언어는 필요하지 않습니다.
• 한 your_table번의 원자 조작으로 완전히 올바른 레코드가 삽입됩니다 .

---

테이블이이라고 가정하십시오 user(id, user_name, user_email).

이 쿼리를 사용할 수 있습니다 :

INSERT INTO user (SELECT NULL,user_name, user_email FROM user WHERE id = 1)

---

이것은 도움이되었으며 BLOB / TEXT 열을 지원합니다.

CREATE TEMPORARY TABLE temp_table
AS
SELECT * FROM source_table WHERE id=2;
UPDATE temp_table SET id=NULL WHERE id=2;
INSERT INTO source_table SELECT * FROM temp_table;
DROP TEMPORARY TABLE temp_table;
USE source_table;

---

열 이름을 지정할 필요가없는 빠르고 깨끗한 솔루션을 위해 https://stackoverflow.com/a/23964285/292677에 설명 된대로 준비된 명령문을 사용할 수 있습니다.

If you need a complex solution so you can do this often, you can use this procedure:

DELIMITER $$

CREATE PROCEDURE `duplicateRows`(_schemaName text, _tableName text, _whereClause text, _omitColumns text)
SQL SECURITY INVOKER
BEGIN
  SELECT IF(TRIM(_omitColumns) <> '', CONCAT('id', ',', TRIM(_omitColumns)), 'id') INTO @omitColumns;

  SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns 
  WHERE table_schema = _schemaName AND table_name = _tableName AND FIND_IN_SET(COLUMN_NAME,@omitColumns) = 0 ORDER BY ORDINAL_POSITION INTO @columns;

  SET @sql = CONCAT('INSERT INTO ', _tableName, '(', @columns, ')',
  'SELECT ', @columns, 
  ' FROM ', _schemaName, '.', _tableName, ' ',  _whereClause);

  PREPARE stmt1 FROM @sql;
  EXECUTE stmt1;
END

You can run it with:

CALL duplicateRows('database', 'table', 'WHERE condition = optional', 'omit_columns_optional');

Examples

duplicateRows('acl', 'users', 'WHERE id = 200'); -- will duplicate the row for the user with id 200
duplicateRows('acl', 'users', 'WHERE id = 200', 'created_ts'); -- same as above but will not copy the created_ts column value    
duplicateRows('acl', 'users', 'WHERE id = 200', 'created_ts,updated_ts'); -- same as above but also omits the updated_ts column
duplicateRows('acl', 'users'); -- will duplicate all records in the table

DISCLAIMER: This solution is only for someone who will be repeatedly duplicating rows in many tables, often. It could be dangerous in the hands of a rogue user.

---

You can also pass in '0' as the value for the column to auto-increment, the correct value will be used when the record is created. This is so much easier than temporary tables.

Source: Copying rows in MySQL (see the second comment, by TRiG, to the first solution, by Lore)

---

insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

---

A lot of great answers here. Below is a sample of the stored procedure that I wrote to accomplish this task for a Web App that I am developing:

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- Create Temporary Table
SELECT * INTO #tempTable FROM <YourTable> WHERE Id = Id

--To trigger the auto increment
UPDATE #tempTable SET Id = NULL 

--Update new data row in #tempTable here!

--Insert duplicate row with modified data back into your table
INSERT INTO <YourTable> SELECT * FROM #tempTable

-- Drop Temporary Table
DROP TABLE #tempTable

---

I was looking for the same feature but I don't use MySQL. I wanted to copy ALL the fields except of course the primary key (id). This was a one shot query, not to be used in any script or code.

I found my way around with PL/SQL but I'm sure any other SQL IDE would do. I did a basic

SELECT * 
FROM mytable 
WHERE id=42;

Then export it to a SQL file where I could find the

INSERT INTO table (col1, col2, col3, ... , col42) 
VALUES (1, 2, 3, ..., 42);

I just edited it and used it :

INSERT INTO table (col1, col2, col3, ... , col42) 
VALUES (mysequence.nextval, 2, 3, ..., 42);

---

I tend to use a variation of what mu is too short posted:

INSERT INTO something_log
SELECT NULL, s.*
FROM something AS s
WHERE s.id = 1;

As long as the tables have identical fields (excepting the auto increment on the log table), then this works nicely.

Since I use stored procedures whenever possible (to make life easier on other programmers who aren't too familiar with databases), this solves the problem of having to go back and update procedures every time you add a new field to a table.

It also ensures that if you add new fields to a table they will start appearing in the log table immediately without having to update your database queries (unless of course you have some that set a field explicitly)

Warning: You will want to make sure to add any new fields to both tables at the same time so that the field order stays the same... otherwise you will start getting odd bugs. If you are the only one that writes database interfaces AND you are very careful then this works nicely. Otherwise, stick to naming all of your fields.

Note: On second thought, unless you are working on a solo project that you are sure won't have others working on it stick to listing all field names explicitly and update your log statements as your schema changes. This shortcut probably is not worth the long term headache it can cause... especially on a production system.

---

INSERT INTO `dbMyDataBase`.`tblMyTable` 
(
    `IdAutoincrement`, 
    `Column2`, 
    `Column3`, 
    `Column4` 
) 

SELECT 
    NULL,  
    `Column2`, 
    `Column3`, 
    'CustomValue' AS Column4 
FROM `dbMyDataBase`.`tblMyTable` 
WHERE `tblMyTable`.`Column2` = 'UniqueValueOfTheKey' 
; 
/* mySQL 5.6 */

---

Dump the row you want to sql and then use the generated SQL, less the ID column to import it back in.

참고URL : https://stackoverflow.com/questions/9156340/how-to-copy-a-row-and-insert-in-same-table-with-a-autoincrement-field-in-mysql