MySQL Insert with functionality similar to Window's default file naming

Question

I have a table with a name column. Initially a name is not added to the table but I would like to add a default name when a new row is inserted, much like window's functionally when creating a new file. I'm trying to figure out how to query the number which is suppose to be next in the sequence.

For example, if my table looks like this:

id | name
==========
1  | New Name (1)
2  | real name
3  | New Name

The next inserted row's name should be "New Name (2)". If my table looks like this:

id | name
==========
1  | New Name (2)
2  | real name

or this:

id | name
==========
1  | another name
2  | real name

The next inserted row's name should be "New Name". If my table looks like this:

id | name
==========
1  | New Name (2)
2  | real name
3  | New Name
4  | New Name (3)

The next inserted row's name should be "New Name (1)". Thus far I was able to create the query to get the existing numbers ("New Name" = 0)

    SELECT SUBSTRING_INDEX(SUBSTR(d.name,INSTR(d.name,'(') + 1), ')', 1)
    FROM data d
    WHERE d.widget_name REGEXP '^New[[:space:]]Name[[:space:]]\\([[:digit:]]\\)$'
    UNION 
    SELECT 0 
    FROM data d
    WHERE d.name REGEXP '^New[[:space:]]Name$'

Now I need a way to to turn that list of numbers to a single number which will indicate whats the next default name enumeration. I've tried using NOT EXISTS from this question but I couldn't figure out how to use the code above both in FROM and the WHERE.

I also tried to do this by creating a row_num functionality using a_horse_with_no_name's answer in this question. Assuming num_data is the result of the query(trying to keep it clean) table and it's column name is name_num:

  SELECT IFNULL(row_number, (SELECT IFNULL(MAX(name_num),0)
                             FROM num_data))
  FROM (SELECT @rownum:=@rownum + 1 AS row_number,  t.*
        FROM (num_data) t,
        (SELECT @rownum := 0) r) gap_table
  WHERE gap_table.row_number <> gap_table.widget_num
  ORDER by row_number
  LIMIT 1;

But that didn't seem to get it right too.

Gordon Linoff · Answer 1 · 2016-06-11T19:03:30.030

1

If you want the next name for a given name, I would expect the query to look like this:

select (case when count(*) = 0 then $name
             else concat($name, '(',
                         max(substring_index(name, ' (', -1) + 0) + 1,
                         ')')
        end)
from num_data
where name rlike concat($name, ' [(][0-9]+[)]' or
      name = $name;

Here is a SQL Fiddle demonstrating it.

This assumes that name being tested is provided by a parameter called $name.

Note: The above query generates the new name. It should be obvious how to get the next number in sequence.

edited Jun 11 '16 at 19:03

answered Jun 11 '16 at 12:17

Gordon Linoff

1,242,037
58
646
786

Hmm something seems to be missing, I've tried playing around with the query, the where clause didn't work so I used mine instead. its missing a case of what happens when you don't have a $name without a number that can be fix by adding this `when max(name REGEXP $name = 0) then $name`. But it appears that the query doesn't fill the gaps because of the `max`. – Kostya Sydoruk Jun 11 '16 at 13:11
@GorodonLinoff Almost, it doesn't fill the gaps and it didn't handle the case of not having $name while already having $name(1) and so on. I modified your query to support it. – Kostya Sydoruk Jun 12 '16 at 10:47

score 0 · Accepted Answer · answered Jun 12 '16 at 11:30

This did the trick, thanks to @GorodonLinoff's answer:

select (case when count(*) = 0 then '$name'
             when max(name = '$name') = 0 then '$name'
             when max(name = '$name(1)') = 0 then '$name(1)'
             else concat('$name', '(', max(substring_index(name, '(', -1) + 0) + 1, ')')
        end)
from data
where name rlike concat('$name', '[(][0-9]+[)]') or
      name = '$name';

MySQL Insert with functionality similar to Window's default file naming

2 Answers2