Insert ordinal number

MySQL 5.7

Consider the following sample data:

CREATE TABLE catalog_product_entity_media_gallery (
  `value` VARCHAR(24),
  `entity_id` INTEGER
);

INSERT INTO catalog_product_entity_media_gallery
  (`value`, `entity_id`)
VALUES
  ('a01-some-item-p1-png.png', '1'),
  ('a01-some-item-p2-png.png', '1'),
  ('a01-some-item-d1-png.png', '1'),
  ('a01-some-item-d5-png.png', '1'),
  ('another-transparent.png', '2'),
  ('another-back.png', '2'),
  ('another-front.png', '2'),
  ('another-side.png', '2');

CREATE TABLE catalog_product_entity (
  `entity_id` INTEGER,
  `sku` VARCHAR(3)
);

INSERT INTO catalog_product_entity
  (`entity_id`, `sku`)
VALUES
  ('1', 'a01'),
  ('2', 'b22');

CREATE TABLE catalog_product_entity_varchar (
  `attribute_id` INTEGER,
  `value` VARCHAR(24)
);

INSERT INTO catalog_product_entity_varchar
  (`attribute_id`, `value`)
VALUES
  ('85', 'a01-some-item-p1-png.png'),
  ('85', 'another-transparent.png');

DB Fiddle of same: https://www.db-fiddle.com/f/7fAx1waY3TwjR34PanBkkv/0

With the query below, I get the following result:

select
    a.value as 'original_file_name',
    b.sku,
    if(isnull(c.attribute_id), 0, 1) as 'is_default',
    concat(sku, '_', if(isnull(c.attribute_id), concat('slideshow_', 'x'), 'default_1'), '.', substring_index(a.value, "." , -1)) as 'new_file_name'
from catalog_product_entity_media_gallery a
join catalog_product_entity b on b.entity_id = a.entity_id
left join catalog_product_entity_varchar c on c.attribute_id = 85 and c.value = a.value
order by sku, is_default desc;

+--------------------------+-----+------------+---------------------+
| original_file_name       | sku | is_default | new_file_name       | 
+--------------------------+-----+------------+---------------------+
| a01-some-item-p1-png.png | a01 |          1 | a01_default_1.png   | 
| a01-some-item-p2-png.png | a01 |          0 | a01_slideshow_x.png | 
| a01-some-item-d1-png.png | a01 |          0 | a01_slideshow_x.png | 
| a01-some-item-d5-png.png | a01 |          0 | a01_slideshow_x.png | 
| another-transparent.png  | b22 |          1 | b22_default_1.png   | 
| another-back.png         | b22 |          0 | b22_slideshow_x.png | 
| another-front.png        | b22 |          0 | b22_slideshow_x.png | 
| another-side.png         | b22 |          0 | b22_slideshow_x.png | 
+--------------------------+-----+------------+---------------------+

In the new_file_name column, I want to insert an ordinal number in place of x. It should start at 1 for every new sku.

Wanted result:

a01_default_1.png
a01_slideshow_1.png
a01_slideshow_2.png
a01_slideshow_3.png
b22_default_1.png
b22_slideshow_1.png
b22_slideshow_2.png
b22_slideshow_3.png

Answer

Using a variable that holds a number that increments for every row.

When is_default = 1, the number gets reset.

drop temporary table if exists pictures;
create temporary table pictures
select
    a.entity_id,
    a.value as original_file_name,
    b.sku as sku,
    if(isnull(d.attribute_id), 0, 1) as is_default,
    substring_index(a.value, "." , -1) as file_extension
from catalog_product_entity_media_gallery a
join catalog_product_entity b on b.entity_id = a.entity_id
left join catalog_product_entity_varchar d on d.attribute_id = 85 and d.value = a.value
order by sku, is_default desc;

set @number = 0;

select
    original_file_name,
    sku,
    is_default,
    case
        when
            is_default = 0
            then
            @number := @number + 1
        else @number := 0
    end as number,
    concat(sku, '_', if(is_default, 'default_1', concat('slideshow_', @number)), '.', file_extension) as new_file_name
from pictures;