Can you access the auto increment value in MySQL within one statement?

by

there’s no need to create another table, and max() will have problems acording to the auto_increment value of the table, do this:

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   DECLARE next_id;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
   SET NEW.field = next_id;
END

I declare the next_id variable because usually it will be used in some other way(*), but you could do straight new.field=(select …)

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   SET NEW.field=(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
END

Also in cases of (SELECT string field) you can use CAST value;

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   SET NEW.field=CAST((SELECT aStringField FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl') AS UNSIGNED);
END

(*) To auto-name an image:

SET NEW.field = CONCAT('image_', next_id, '.gif');

(*) To create a hash:

SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );

More Related Contents:

  1. Auto Increment after delete in MySQL
  2. How to insert data to MySQL with auto-incremented column(field)?
  3. MySQL: Alternatives to ORDER BY RAND()
  4. MySQL LIKE IN()?
  5. In MySQL queries, why use join instead of where?
  6. Stored Procedure with optional “WHERE” parameters
  7. mySQL :: insert into table, data from another table?
  8. Return 0 if field is null in MySQL
  9. SQLite – ORDER BY RAND()
  10. How to search multiple columns in MySQL?
  11. How can I INSERT data into two tables simultaneously in SQL Server?
  12. INSERT rows into multiple tables in a single query, selecting from an involved table
  13. MySQL INSERT INTO … VALUES and SELECT
  14. SQL Server 2008 – Help writing simple INSERT Trigger
  15. SQL SELECT to get the first N positive integers
  16. postgresql: INSERT INTO … (SELECT * …)
  17. Sorting string column containing numbers in SQL?
  18. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  19. Multiple Ranks in one table
  20. The difference between ‘AND’ and ‘&&’ in SQL
  21. How can I make an average of dates in MySQL?
  22. What is the best way to generate ranks in MYSQL?
  23. MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) – Unexpected result
  24. How important are lookup tables?
  25. How to find all IDs of children recursively?
  26. MySql bulk load command line tool
  27. ALTER TABLE without locking the table?
  28. MySQL: Typecasting NULL to 0
  29. how to use LIKE with column name
  30. How to implement a Keyword Search in MySQL?

Leave a Comment