DUPLICATE KEY with auto add +1 to the cell?

Question

I have a table, For example: http://rextester.com/MVG66914

User fills in the fields: name_1(fill any text) and name_2(fill 1 or nothing). If the cell name_1 contains the same text - name_2cell should be increased by 1.

To look like this: http://rextester.com/FSI28573

I can't set UNIQUE or PRIMARY. You can see it on the table examples.

But I can set INDEX Maybe like this:

ALTER TABLE `table15` ADD INDEX(`name_1`);

ALTER TABLE `table15` ADD INDEX(`name_2`);

For example:

INSERT INTO table14a (id, name_1, name_2) VALUES (2, name_1, name_2) ON DUPLICATE KEY UPDATE name_2=name_2+1;

http://rextester.com/YJZ25085

But it should need to be increased by 1 automatically, every time, when user creates a new row with identical text in name_1

INSERT INTO productsfp (?) VALUES (?) ON DUPLICATE KEY UPDATE name_2=name_2+1;

So I tried a variations with DUPLICATE KEY But it didn't work.

I asked question: MySql how to use UPDATE with HAVING? But I realized that I was mistaken in forming request. Cuz all values increases by 1, instead of a serial. Then I read MySql documentation about DUPLICATE KEY and are now stuck in a given issue. Does anyone know how to solve this query?

Answer 1

If you specify a composite key involving name_1 and id, then id will auto increment within each name group. The following table definition would achieve this:

CREATE TABLE `table15` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name_1` VARCHAR(70) NOT NULL, 
    PRIMARY KEY(`name_1`, `id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Note that this won't work with InnoDB, but it will work with MyISAM. Also, I removed the name_2 column, because now the id column will track what you intended for name_2.

Further reading: auto_increment by group

Update:

If you still want a continuous, linear, auto increment ID column, then you can compute it while selecting using the following scheme:

CREATE TABLE `table15` (
    `name_2` INT(11) NOT NULL AUTO_INCREMENT,
    `name_id` INT(11) NOT NULL,
    `name_1` VARCHAR(70) NOT NULL,
    PRIMARY KEY(`name_id`, `name_2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT t1.name_2,
       t1.name_id,
       t1.name_1,
       (SELECT COUNT(*) FROM table15 t2
        WHERE t2.name_id < t1.name_id) + t1.name_2 AS id
FROM table15 t1

This assumes that your table would contain a name_id column, which contains integer identifiers for each unique name which appears.

Demo here:

Rextester