Insert row with single column value based on last record

Question

I need to add a new product to the table. But for this I need to check the SKU of the last inserted product and increase it by 1 and insert new product details into it. SKU is something like SKUNXXXXX - X is a number. Changing the structure of the table is not possible.

Possible solutions that I can think of

• Get the last row using order and constraint 1.
• replace "SKUN" with an empty string and increase the number by 1
• Insert a record with product information and increased SKU

But this situation can create a problem (although I'm not sure about it). The problem is that if only after receiving the last record and before inserting new product information, another request comes in and gets the same last record? In this case, both products have the same SKU.

Please let me know how to resolve this situation.

Answer 1

One option is to create an auto-increment column id, and then use it to create SKUfor each product as you insert it. The trigger that will be triggered after INSERTcan then be used to assign SKUto the newly inserted product. But, alas, MySQL does not allow a trigger to modify the table in which it was run.

. SKU SKUNXXXXX, XXXXX - , SKU " ", . , products :

CREATE TABLE products
(
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(55),
    ...
);

, INSERT products, id MySQL. SKU, :

SELECT id, name, CONCAT('SKUN', id) AS `SKU`
FROM products
WHERE name = 'someproduct'

, SKU (.. , ), id, q.v. SO post .