0

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
5
  • 1
    Please post the table definition. Also, is there any value in storing the result of the FLOOR function? Why not just create a view with that column? Jan 15, 2019 at 22:38
  • A view which generates the folder on the fly is certainly a solution, thanks
    – MrMartin
    Jan 15, 2019 at 23:00
  • 1
    Did you realize, in MySql you can actually insert into a view. That way the table would really only store the materialized values passed through the view while the view would generate the derived values on the fly like you said. That is going to be a better solution than trying to generate a value and update after an insert. Jan 15, 2019 at 23:02
  • I didn't know that, and yes, that sounds like the solution I'm looking for.
    – MrMartin
    Jan 15, 2019 at 23:07
  • Ok. I'll write it up as an answer. Please stand by. Jan 15, 2019 at 23:10

2 Answers 2

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.

3
  • I think you mean, "just insert and select from the view", without the need for updating. Right?
    – MrMartin
    Jan 16, 2019 at 21:33
  • The solution does not require an update. But the view does not prevent you from updating. Hopefully I understood what you're asking. Jan 16, 2019 at 21:39
  • Your answer is right, I just think that the last sentence should be "From there, just insert, and select from the view and it should take care of it.". Why do you say "update" here?
    – MrMartin
    Jan 16, 2019 at 22:23
2

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

Plan B: Have folder be a virtual generated column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.