基于其他一些行创建没有真正唯一键的mysql行

Question

Database example:

| country | animal | size   | x_id* |
|---------+--------+--------+-------|
|  777    | 1001   | small  |   1   |
|  777    | 2002   | medium |   2   |
|  777    | 7007   | medium |   3   |
|  777    | 7007   | large  |   4   |
|  42     | 1001   | small  |   1   |
|  42     | 2002   | medium |   2   |
|  42     | 7007   | large  |   4   |

I need to generate the x_id continuously based on entries in (animal, size) and if x_id for the combination x_id exist use it again.

Currently i use the following PHP script for this action, but on a large db table it is very slow.

query("UPDATE myTable SET x_id = -1");

$i = $j;
$c = array();
$q = query("
    SELECT animal, size
      FROM myTable
     WHERE x_id = -1
  GROUP BY animal, size");

while($r = fetch_array($q)) {

  $hk = $r['animal'] . '-' . $r['size'];

  if( !isset( $c[$hk] ) ) $c[$hk] = $i++;

  query("
      UPDATE myTable
       SET x_id = {$c[$hk]}
     WHERE animal = '".$r['animal']."'
       AND size = '".$r['size']."'
       AND x_id = -1");

}

Is there a way to convert the PHP script to one or two mysql commands?

edit:

CREATE TABLE `myTable` (
`country` int(10) unsigned NOT NULL DEFAULT '1', -- country
`animal` int(3) NOT NULL,
`size` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`lang_id` tinyint(4) NOT NULL DEFAULT '1',
`x_id` int(10) NOT NULL,
KEY `country` (`country`),
KEY `x_id` (`x_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

UPDATE myTable m
    JOIN (
        SELECT animal, size, @newid := @newid + 1 AS x_id
        FROM myTable a
            CROSS JOIN (SELECT @newid := 0) b
        WHERE x_id = -1
        GROUP BY animal, size
    ) t ON m.animal = t.animal AND m.size = t.size
SET m.x_id = t.x_id
;

http://sqlfiddle.com/#!9/5525ba/1

The group by in the subquery is not needed. It generates useless overhead. If it's fast enough, leave it like this, otherwise we can use distinct+another subquery instead.