MySQL 테이블에 여러 행을 삽입하고 새 ID를 반환하려면 어떻게 해야 합니까?

MySQL 테이블에 여러 행을 삽입하고 새 ID를 반환하려면 어떻게 해야 합니까?

하여 MySQL을 수 있습니다.last_insert_id하지만 이제 테이블에 많은 행을 대량으로 삽입하고 ID 배열을 가져오고 싶습니다.가가 어떻 는는 는?? ???

비슷한 질문들이 몇 가지 있지만, 정확히 같은 것은 아니다.임시 테이블에 새 ID를 삽입하지 않고 ID 배열을 다시 가져오기만 하면 됩니다.

대량 삽입에서 lastInsertId를 가져올 수 있습니까?

mysql multiple 행 insert-select 문과 last_insert_id()

의 에서 InnoDB 를 는, 「MySQL」, 「MySQL」를 사용해 ID 할 수 .MySQL 의 최신 버전에서 InnoDB 를 사용하고 있는 경우는, 다음의 ID 의 리스트를 취득할 수 있습니다.LAST_INSERT_ID() ★★★★★★★★★★★★★★★★★」ROW_COUNT().

시 합니다.단, InnoDB는 AUTO INCREENT를 사용합니다.innodb_autoinc_lock_mode0(수직) 또는 1(수직)로 설정됩니다.그 결과 첫 번째 ID는LAST_INSERT_ID()그리고 마지막을 덧붙임으로써ROW_COUNT()-1.

삽입된 각 행 세트의 고유 식별자(guid)를 저장한 후 행 ID를 선택하는 방법밖에 없습니다. 예:

INSERT INTO t1
(SELECT col1,col2,col3,'3aee88e2-a981-1027-a396-84f02afe7c70' FROM a_very_large_table);
COMMIT;

SELECT id FROM t1 
WHERE guid='3aee88e2-a981-1027-a396-84f02afe7c70';

GUID를 .uuid()

2개의 cols uid, col1을 가진 temptable이라는 테이블이 있다고 합니다.uid는 자동 증분 필드입니다.다음과 같은 작업을 수행하면 결과 세트에 삽입된 ID가 모두 반환됩니다.결과 세트를 루프하여 ID를 얻을 수 있습니다.이것은 오래된 투고이며, 이 솔루션이 모든 경우에 효과가 있는 것은 아니라는 것을 알고 있습니다.하지만 다른 사람들은 그럴 수 있고 그래서 내가 그것에 답하는 거야.

# lock the table
lock tables temptable write;

#bulk insert the rows;
insert into temptable(col1) values(1),(2),(3),(4);

#get the value of first inserted row. when bulk inserting last_insert_id() #should give the value of first inserted row from bulk op.
set @first_id = last_insert_id();

#now select the auto increment field whose value is greater than equal to #the first row. Remember since you have write lock on that table other #sessions can't write to it. This resultset should have all the inserted #id's
select uid from temptable where uid >=@first_id;

#now that you are done don't forget to unlock the table.
unlock tables;

이 실은 오래되었지만 이 모든 해결책들은 나에게 도움이 되지 않아서 나는 나만의 것을 생각해냈다.

먼저 삽입할 행 수를 세십시오.

예를 들어 5개의 행을 추가해야 합니다.

LOCK TABLE tbl WRITE;

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME   = 'tbl'

그런 다음 방금 선택한 auto_module을 사용하여 다음 쿼리를 수행합니다.

ALTER TABLE tbl AUTO_INCREMENT = {AUTO_INCREMENT}+5;
UNLOCK TABLES;

마지막으로 삽입을 합니다.

예약된 자동 증가 범위를 사용하여 ID를 삽입하십시오.

경고: 이 솔루션을 사용하려면 테이블에 대한 액세스 수준을 높여야 합니다.그러나 보통 대량 삽입은 cron 및 Import 스크립트에 의해 실행되며, 이 스크립트는 특별한 액세스를 사용할 수 없습니다.몇 가지 삽입에만 사용할 수는 없습니다.

중복 키 업데이트 시 사용할 경우 사용되지 않는 ID가 남아 있을 수 있습니다.

@Hansen의 은 @Dag Sondre Hansen이 있는 할 수 innodb_autoinc_lock_mode로 2삽입하기 전에 테이블을 잠그는 것만으로 충분합니다.

LOCK TABLE my_table WRITE;
INSERT INTO my_table (col_a, col_b, col_c) VALUES (1,2,3), (4,5,6), (7,8,9);
SET @row_count = ROW_COUNT();
SET @last_insert_id = LAST_INSERT_ID();
UNLOCK TABLES;
SELECT id FROM my_table WHERE id >= @last_insert_id AND id <= @last_insert_id + (@row_count - 1);

다음은 바이올린 시연입니다.https://www.db-fiddle.com/f/ahXAhosYkkRmwqR9Y4mAsr/0

이것을 완벽하게 하기 위해서는 어플리케이션 내의 트랜잭션 ID 또는 어플리케이션 내의 아이템 ID 중 하나를 취급해야 한다고 생각합니다.

모든 삽입이 성공했다고 가정할 때(!) 동작할 수 있는 방법 중 하나는 다음과 같습니다.

그런 다음 lastid(벌크 삽입의 첫 번째 삽입 ID)부터 시작하여 영향을 받는 행 수에 대한 루프가 포함된 삽입 ID를 가져올 수 있습니다.그래서 완벽하게 동작하는 것을 확인했습니다.예를 들어 HeidiSQL이 ROW_COUNT()에 대해 올바른 값을 반환하지 않도록 주의해 주십시오.아마도 랜덤한 일을 하고 있는 형편없는 GUI이기 때문일 것입니다.단, 커맨드 라인이나 PHP mysqli에서는 완전히 정확합니다.

START TRANSACTION;
BEGIN;
INSERT into test (b) VALUES ('1'),('2'),('3');
SELECT LAST_INSERT_ID() AS lastid,ROW_COUNT() AS rowcount;
COMMIT;

PHP에서는 다음과 같습니다(local_sqle은 mysqli_query에 대한 직접 호출, local_sqlec는 mysqli_query에 대한 호출 + 결과 세트를 PHP 어레이로 변환).

local_sqle("START TRANSACTION;
BEGIN;
INSERT into test (b) VALUES ('1'),('2'),('3');");
$r=local_sqlec("SELECT LAST_INSERT_ID() AS lastid,ROW_COUNT() AS rowcount;");
local_sqle("
COMMIT;");
$i=0;
echo "last id =".($r[0]['lastid'])."<br>";
echo "Row count =".($r[0]['rowcount'])."<br>";

while($i<$r[0]['rowcount']){
    echo "inserted id =".($r[0]['lastid']+$i)."<br>";
    $i++;
}

쿼리가 분리되는 이유는 다른 경우 내 함수를 사용하여 결과를 얻을 수 없기 때문입니다. 표준 함수를 사용하여 결과를 가져오면 하나의 스테이트먼트에 다시 넣고 필요한 결과를 얻을 수 있습니다(결과 번호 2 - 결과 세트/쿼리를 여러 개 처리하는 확장을 사용하는 경우).

auto increment value가 항목 1만큼 증가할지 확신할 수 없습니다.또한 DB에 마스터 // 마스터 리플리케이션이 있고 auto_increment 중복 제외가 해결되면 큰 문제가 발생합니다.AI는 +1이 아닌 +2가 되고 마스터가 하나 더 있으면 +3이 됩니다.따라서 AUTO_INCREMENT가 1로 올라간다는 등의 릴레이 온은 프로젝트를 정지시킵니다.

그렇게 할 수 있는 좋은 방법밖에 없어요.

이 SQL 스니펫은 여러 마스터에 문제가 없으며 삽입된 레코드만 필요할 때까지 좋은 결과를 제공합니다.트랜잭션 없이 여러 요청에 대해 다른 삽입 레코드를 캡처할 수 있습니다.

START TRANSACTION;
SELECT max(id) into @maxLastId FROM `main_table`;
INSERT INTO `main_table` (`value`) VALUES ('first'), ('second') ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);
SELECT `id` FROM `main_table` WHERE id > @maxLastId OR @maxLastId IS NULL;
COMMIT;

(DUPLICE KEY UPDATE에 의한 갱신 레코드도 필요한 경우) 데이터베이스를 약간 리팩터링해야 합니다.SQL은 (트랜잭션에 대해 안전하고 한 연결 내에 트랜잭션이 없습니다.)

#START TRANSACTION    
INSERT INTO bulk_inserts VALUES (null);
SET @blukTransactionId = LAST_INSERT_ID();
SELECT  @blukTransactionId, LAST_INSERT_ID();
INSERT INTO `main_table` (`value`, `transaction_id`) VALUES ('first', @blukTransactionId), ('second', @blukTransactionId) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`), `transaction_id` = VALUES(`transaction_id`);
SELECT  @blukTransactionId, LAST_INSERT_ID();
SELECT id FROM `main_table` WHERE `transaction_id` = @blukTransactionId;
#COMMIT

두 사건 모두 초국가적으로 안전합니다첫 번째는 삽입된 레코드만 표시하고 두 번째는 모든 레코드가 갱신된 상태로 표시됩니다.

또한 이러한 옵션은 INSERT IGNORE...를 사용해도 작동합니다.

JDBC와 함께 Java를 사용하는 사용자는 누구나 가능합니다.다음과 같이 배치 삽입하여 ID를 복구합니다.

PreparedStatement insertBatch = null;
Connection connection = ....;

for (Event event : events) {
    
        if (insertBatch == null){
            insertBatch = connection.prepareStatement("insert into `event` (game, `type`, actor, target, arg1, arg2, arg3, created) " +
                "values (?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
        }
        
        insertBatch.setObject(1, event.game);
        insertBatch.setString(2, event.type);
        insertBatch.setObject(3, event.actor);
        insertBatch.setObject(4, event.target);
        insertBatch.setString(5, event.arg1);
        insertBatch.setObject(6, event.arg2);
        insertBatch.setObject(7, event.arg3);
        insertBatch.setTimestamp(8, new Timestamp(event.created.getTime()));
        insertBatch.addBatch();
    }
}

if (insertBatch != null){
    insertBatch.executeBatch();
    ResultSet generatedKeys = insertBatch.getGeneratedKeys();
    
    for (Event event : events) {

        if ( generatedKeys == null || ! generatedKeys.next()){
            logger.warn("Unable to retrieve all generated keys");
        }
        event.id = generatedKeys.getLong(1);
    }
    
    logger.debug("events inserted");
}

출처: "MySQL을 사용하여 JDBC에서 이 방법으로 실행할 수 있습니다." - PLAP - https://groups.google.com/g/jdbi/c/ZDqnfhK758g?pli=1

실제로 JDBC URL에 rewriteBatchedStatements=true를 추가해야 합니다.그렇지 않으면 실제 삽입이 mysql "general query log"에 별도의 행으로 표시됩니다.7000개의 행을 삽입하면 일반 삽입용으로 2m11s, 다시 쓰지 않고 46s를 얻을 수 있습니다.1.1s를 켜서 다시 씁니다.켜짐. 또한 다른 사람의 삽입물이 차단되지 않습니다(테스트했습니다).20만 행을 삽입했을 때 한 줄에 약 36만 행으로 그룹화되었습니다.insert into abc(..) values(..),(..),(..)....

실제로 JDB를 사용하고 있습니다.Prepared Statement에 액세스하는 방법은 다음과 같습니다.

ArrayList<Long> generatedIds = (ArrayList<Long>) jdbcTemplate.execute(
    new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            return connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
        }
    },
    new PreparedStatementCallback<Object>() {
        @Override
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            // see above answer for setting the row data
            ...
            ps.executeBatch();

            ResultSet resultSet = ps.getGeneratedKeys();
            ArrayList<Long> ids = new ArrayList<>();
            while (resultSet.next()) {
                ids.add(resultSet.getLong(1));
            }
            return ids;
        }
    }
);

이 문제는 삽입한 행의 수를 알고 있는 경우 다른 방법으로 해결할 수 있습니다.예를 들어, 4개의 행을 삽입하면 테이블의 마지막 ID는600.

따라서 삽입 쿼리를 수행한 후 바로

select last_insert_id()

첫 번째 삽입 ID를 얻으려면 이제 이 간단한 계산을 수행할 수 있습니다.

last_insert_id()가치가 있는 것 같은 가치601그래서 여기라면601 + 4 - 1=604

그래서, 모든 ID는601그리고.604

InnoDB가 필요.트랜잭션에 모든 쿼리를 넣어야 합니다.

편집-
해결책 2 varchar 또는 텍스트필드가 있는 경우에만 동작합니다.varchar 또는 텍스트필드에 임의의 문자열을 추가합니다.예를 들어 랜덤 문자열은123sdfsklls113당신은 가지고 있다name값이 다음과 같은 필드Sam이 경우 필드는 다음과 같이 됩니다.

sam123sdfsklls113

랜덤 문자열을 열 값의 끝에 추가합니다.

행을 삽입한 후 다음을 사용하여 마지막으로 삽입된 행을 쉽게 가져올 수 있습니다.

select id from table_name where column_name like '%randomstring_here'

그러면 ID를 가져온 후 다음과 같이 열 값을 쉽게 업데이트할 수 있습니다.

update table_name set column_name = replace(column_name, 'randomstring_ here', '') where id in (last inserted ids here)

컬럼 값에서 랜덤 문자열이 삭제됩니다.

$query = "INSERT INTO TABLE (ID,NAME,EMAIL) VALUES (NULL,VALUE1, VALUE2)";
$idArray = array();
foreach($array as $key) {
 mysql_query($query);
 array_push($idArray, mysql_insert_id());
}
print_r($idArray);

언급URL : https://stackoverflow.com/questions/7333524/how-can-i-insert-many-rows-into-a-mysql-table-and-return-the-new-ids