InnoDB is a transactional engine. This means that in the following scenario: Session A inserts record 1 Session B inserts record 2 Session A rolls back , there is either a possibility of a gap or session B would lock until the session A committed or rolled back. InnoDB designers (as most of the other … Read more
As selected answer says you can use findAndModify to generate sequential IDs. But I strongly disagree with opinion that you should not do that. It all depends on your business needs. Having 12-byte ID may be very resource consuming and cause significant scalability issues in future. I have detailed answer here.
Use the DEFAULT keyword: $query = “INSERT INTO myTable VALUES (DEFAULT,’Fname’, ‘Lname’, ‘Website’)”; Also, you can specify the columns, (which is better practice): $query = “INSERT INTO myTable (fname, lname, website) VALUES (‘fname’, ‘lname’, ‘website’)”; Reference: http://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html
This will give you a consecutive row number with 3. SELECT (@cnt := @cnt + 1) AS rowNumber, t.rowID FROM myTable AS t CROSS JOIN (SELECT @cnt := 0) AS dummy WHERE t.CategoryID = 1 ORDER BY t.rowID ; Result | ROWNUMBER | ROWID | ——————— | 1 | 1 | | 2 | 25 … Read more
Just use NULL as your first value, the autoincrement field will still work as expected: INSERT INTO tblname VALUES (NULL, … 32 Fields … )
For SQL 2005 and up SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS ‘rownumber’,* FROM YourTable for 2000 you need to do something like this SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE INTO #Ranks FROM YourTable WHERE 1=0 INSERT INTO #Ranks SELECT SomeColumn FROM YourTable ORDER BY SomeColumn SELECT * FROM #Ranks Order By Ranks see … Read more
The currval will return the last value generated for the sequence within the current session. So if another session generates a new value for the sequence you still can retrieve the last value generated by YOUR session, avoiding errors. But, to get the last generated value on any sessions, you can use the above: SELECT … Read more
The difference is purely in the maximum value which can be stored (18,446,744,073,709,551,615 for the bigint(20) and 4,294,967,295 for the int(10), I believe), as per the details on the MySQL Numeric Types manual page. Incidentally, the use of (20) and (10) is largely irrelevant unless you’re using ZEROFILL. (i.e.: It doesn’t actually change the size … Read more
Have you tried indicating to which fields of the table the parameters you are passing are supposed to be related? INSERT INTO table_name (column1, column2, column3,…) VALUES (value1, value2, value3,…) In your case maybe something like: INSERT INTO participants(col1, col2) VALUES (“bla”,”blub”);
It can be done in a single command. You need to set the IDENTITY property for “auto number”: ALTER TABLE MyTable ADD mytableID int NOT NULL IDENTITY (1,1) PRIMARY KEY More precisely, to set a named table level constraint: ALTER TABLE MyTable ADD MytableID int NOT NULL IDENTITY (1,1), CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (MyTableID) … Read more