OUTPUT Inserted.row in mysqli

I have the following sql table:

id|email|fbid

When I perform the query

 INSERT INTO users(email,fbid) VALUES('randomvalue','otherrandomvalue')

I want to get the id of the inserted row. To do so, I've tried to edit the query like this:

 INSERT INTO users(email,fbid) VALUES('randomvalue','otherrandomvalue') OUTPUT Inserted.id

But I'm getting:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTPUT Inserted.id' at line 1

What could be the problem?

回答1:

Unfortunately (as far as I can tell) mysql does not support output as sql-server does.

You do have an option for what you're trying to accomplish in a single row insert (assuming auto_increment primary key):

SELECT LAST_INSERT_ID();

This unfortunately would not work in the case of a batch insert - though in your case you are not (at least not in your example), so this should be fine.

回答2:

I'm going to use the process i describe below to handle the same situation with a private at home (non-enterprise) application that i wrote for personal use. I know this question is a year old right now but there doesn't seem to be an adequate answer for batch processing. I can't find an adequate answer. MySQL doesn't seem to have the facilities built into it to handle this type of thing.

I had concerns about the reliability of this solution, when put into a production environment where multiple different users/jobs could access the same procedure at the same time to do the insert. I believe I have resolved these concerns by adding the connection id to the @by variable assignment. Doing this makes it so that the by has a: the connection id for the session and b: the name of the program/job/procedure doing the insert. Combined with the date AND time of the insert, I believe these three values provide a very secure key to retrieve the correct set of inserted rows. If absolute certainty is required for this, you could possibly add a third column of a GUID type (or varchar) generate a GUID variable to insert into that, then use the GUID variable along with @by and @now as your key. I feel it's unnecessary for my purpose because the process I'm going to use it in is an event (job) script that runs on the server rather than in PHP. So I am not going to exemplify it unless someone asks for that.

WARNING

If you are doing this in PHP, consider using a GUID column in your process rather than the CreatedBy. It's important that you do that in PHP because your connection can be lost in between inserting the records and trying to retrive the IDS and your CreatedBy with the connection ID will be rendered useless. If you have a GUID that you create in PHP, however, you can loop until your connection succeeds or recover using the GUID that you saved off somewhere in a file. The need for this level of connection security is not necessary for my purposes so I will not be doing this.

The key to this solution is that CreatedBy is the connection id combined with the name of the job or procedure that is doing the insert and CreatedDate is a CURRENT_TIMESTAMP that is held inside a variable that is used through the below code. Let's say you have a table named "TestTable". It has the following structure:

Test "Insert Into" table

CREATE TABLE TestTable (
      TestTableID INT NOT NULL AUTO_INCREMENT
    , Name VARCHAR(100) NOT NULL
    , CreatedBy VARCHAR(50) NOT NULL
    , CreatedDate DATETIME NOT NULL
    , PRIMARY KEY (TestTableID)
);

Temp table to store inserted ids

This temporary table will hold the primary key ids of the rows inserted into TestTable. It has a simple structure of just one field that is both the primary key of the temp table and the primary key of the inserted table (TestTable)

DROP TEMPORARY TABLE IF EXISTS tTestTablesInserted;
CREATE TEMPORARY TABLE tTestTablesInserted(
      TestTableID INT NOT NULL
    , PRIMARY KEY (TestTableID)
);

Variables

This is important. You need to store the CreatedBy and CreatedDate in a variable. CreatedBy is stored for consistency/coding practices, CreatedDate is very important because you are going to use this as a key to retrieve the inserted rows.

An example of what @by will look like: CONID(576) BuildTestTableData

Note that it's important to encapsulate the connection id with something that indicates what it is since it's being used as a "composite" with other information in one field

An example of what @now will look like: '2016-03-11 09:51:10'

Note that it's important to encapsulate @by with a LEFT(50) to avoid tripping a truncation error upon insert into the CreatedBy VARCHAR(50) column. I know this happens in sql server, not so sure about mysql. If mysql does not throw an exception when truncating data, a silent error could persist where you insert a truncated value into the field and then matches for the retrieval fail because you're trying to match a non-truncated version of the string to a truncated version of the string. If mysql doesn't truncate upon insert (i.e. it does not enforce type value restrictions) then this is not a real concern. I do it out of standard practice from my sql server experience.

SET @by = LEFT(CONCAT('CONID(', CONNECTION_ID(), ') BuildTestTableData'), 50);
SET @now = CURRENT_TIMESTAMP;

Insert into TestTable

Do your insert into test table, specifying a CreatedBy and CreatedDate of @by and @now

INSERT INTO TestTable (
      Name
    , CreatedBy
    , CreatedDate
)
SELECT Name
     , @by
     , @now
FROM SomeDataSource
WHERE BusinessRulesMatch = 1
;

Retrieve inserted ids

Now, use @by and @now to retrieve the ids of the inserted rows in test table

INSERT INTO tTestTablesInserted (TestTableID)
SELECT TestTableID
FROM TestTable
WHERE CreatedBy = @by
  AND CreatedDate = @now
;

Do whatever with retreived information

/*DO SOME STUFF HERE*/
SELECT * 
FROM tTestTablesInserted tti
JOIN TestTable tt
    ON tt.TestTableID = tti.TestTableID
;

回答3:

if You are using php then it is better to use following code :

 if ($conn->query($sql) === TRUE) {
  $last_id = $conn->insert_id;
    echo "New record created successfully. Last inserted ID is: " . $last_id;
 } else {
   echo "Error: " . $sql . "<br>" . $conn->error;
  }

where $conn is connection variable.