maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

It further depends on auto increment lock mode.  You can have a big gap between "buckets" of id values in the non-default mode, if the bulk insert is large and uses more than one "bucket" of values.

Sent from my iPhone

> On Aug 26, 2016, at 12:46 PM, Justin Swanhart <greenlion@xxxxxxxxx> wrote:
> 
> Generated id values are not guaranteed to be sequential.  The auto_increment lock is shared by transactions.  This code will not always work.
> 
> Sent from my iPhone
> 
>> On Aug 26, 2016, at 12:33 PM, Diego Dupin <diego.dupin@xxxxxxxxxxx> wrote:
>> 
>> Hi peter,
>> 
>> Basically, server will return different informations to driver. In those informations, there is a "last insert id" that correspond to the first created id of the query, and the number of affected rows.
>> So for example :    
>> try (Statement stmt = sharedConnection.createStatement()) {
>>     stmt.executeUpdate("INSERT INTO demo (name) VALUES ('Jeff'),('Jones'),('Agnes'),('Wolfgang')", Statement.RETURN_GENERATED_KEYS);
>>     try (ResultSet rs = stmt.getGeneratedKeys()) {
>>         while (rs.next()) {
>>             System.out.println(rs.getString(1));
>>         }
>>     }
>> }
>> server will return for example "last insert id" 1 and 4 new rows. 
>> 
>> Driver will reconstruct the inserted Id according to @@auto_increment_increment.
>> will return 
>> 1
>> 2 (= 1 + @@auto_increment_increment)
>> 3 (= 1 + 2 * @@auto_increment_increment)
>> 4 (= 1 + 3 * @@auto_increment_increment)
>> 
>> In JDBC, standard way is to use PrepareStatement (and it's better to avoid injection than created a String like the previous one) and batch methods. 
>> Example : 
>> String[] names = new String[] {"Jeff", "Jones", "Agnes", "Wolfgang"};
>> try (PreparedStatement pstmt = sharedConnection.prepareStatement("INSERT INTO demo (name) VALUES (?)", Statement.RETURN_GENERATED_KEYS)) {
>>     for (String name : names) {
>>         pstmt.setString(1, name);
>>         pstmt.addBatch();
>>     }
>>     pstmt.executeBatch();
>> 
>>     try (ResultSet rs = pstmt.getGeneratedKeys()) {
>>         while (rs.next()) {
>>             System.out.println(rs.getString(1));
>>         }
>>     }
>> }
>> Server will send to driver informations for each insert in the order corresponding to the addBatch() methods.
>> There is no reconstruction this way. 
>> 
>> I'll add this to documentation next week
>> 
>> diego
>> 
>> 
>> 
>>> On Thu, Aug 25, 2016 at 4:38 PM, Péter Éberhardt <eberhardt.peter@xxxxxxxxx> wrote:
>>> Hi,
>>> 
>>> I want to insert multiple records into a table using one statement
>>> like this through jdbc:
>>> 
>>>   INSERT INTO demo (name) VALUES ('Jeff'),('Jones'),('Agnes'),...,('Wolfgang');
>>> 
>>> Then I want to get the generated ids with the getGeneratedKeys()
>>> method and match them to the inserted records but how can I be sure
>>> the method gives the ids in the same order as I listed the records in
>>> the statement? I can't find any related information in the
>>> documentation just the fact the method gives back those ids.
>>> 
>>> DDL:
>>> 
>>> CREATE TABLE demo (id bigint primary key auto_increment , name text);
>>> 
>>> If this information would put into the document also then I can go
>>> with my implementation on this way.
>>> 
>>> Thanks,
>>> Peter
>>> 
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~maria-discuss
>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>> More help   : https://help.launchpad.net/ListHelp
>> 
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp