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

i have table name column. name not added table add default name when new row inserted, window's functionally when creating new file. i'm trying figure out how query number suppose next in sequence.

for example, if table looks this:

id | name ========== 1  | new name (1) 2  | real name 3  | new name 

the next inserted row's name should "new name (2)". if table looks this:

id | name ========== 1  | new name (2) 2  | real name 

or this:

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

the next inserted row's name should "new name". if table looks this:

id | name ========== 1  | new name (2) 2  | real name 3  | new name 4  | new name (3) 

the next inserted row's name should "new name (1)". far able create query existing numbers ("new name" = 0)

    select substring_index(substr(d.name,instr(d.name,'(') + 1), ')', 1)     data d     d.widget_name regexp '^new[[:space:]]name[[:space:]]\\([[:digit:]]\\)$'     union      select 0      data d     d.name regexp '^new[[:space:]]name$'  

now need way to turn list of numbers single number indicate whats next default name enumeration. i've tried using not exists from question couldn't figure out how use code above both in from , where.

i tried creating row_num functionality using a_horse_with_no_name's answer in question. assuming num_data result of query(trying keep clean) table , it's column name name_num:

  select ifnull(row_number, (select ifnull(max(name_num),0)                              num_data))   (select @rownum:=@rownum + 1 row_number,  t.*         (num_data) t,         (select @rownum := 0) r) gap_table   gap_table.row_number <> gap_table.widget_num   order row_number   limit 1; 

but didn't seem right too.

if want next name given name, expect query this:

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

here sql fiddle demonstrating it.

this assumes name being tested provided parameter called $name.

note: above query generates new name. should obvious how next number in sequence.