MySQL insert with value derived from primary id

Question

I'd like to INSERT into a MySQL table, in such a way that one of the columns holds a value derived from the primary id.

For example:

insert into picture_table ('picture name','folder') values ('holiday_123.png',FLOOR(pic_id/1000)

Where pic_id is the auto_incrementing primary id of the table. It would be even better if this was somehow hard-wired into the table. Can this be done?

The best I've come up with is insert, then update using last insert ID

The table may look like this:

CREATE TABLE IF NOT EXISTS `picture_table` (
  `pic_id` int(8) NOT NULL AUTO_INCREMENT,
  `picture_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `folder` varchar(8) NOT NULL,
  (...other stuff...)
  PRIMARY KEY (`pic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

Answer 1

A MySql view would be a great solution here. Instead of storing the value (which is just derived from the id), a function can easily and quickly generate on the fly in a view. It's just as fast and avoids unnecessary IO on the database.

CREATE VIEW picture_view AS
SELECT pic_id, picture_name
  , FLOOR(pic_id/1000) AS folder
FROM picture_table

From there, just insert and select from the view and it should take care of it.

Answer 2

Plan A: Use a TRIGGER to compute the value for folder.

Plan B: Have folder be a virtual generated column.