I have a table:

CREATE TABLE `payments` ( `id` int(9) unsigned zerofill NOT NULL AUTO_INCREMENT, ... `payments_types_id` smallint(3) unsigned NOT NULL DEFAULT '1', ... `service_id` int(9) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `index_service_id` (`service_id`,`payments_types_id`), ) ENGINE=InnoDB AUTO_INCREMENT=900199076 DEFAULT CHARSET=cp1251

The problem concerns the index index_service_id . I want to insert data into the table without filling in the service_id field, and then update the table, making the value of this field equal to the value of the id field, which will ensure the uniqueness of the index index_service_id . At the time of inserting and updating the table, I disable the uniqueness of indexes:

 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; LOCK TABLES `payments` WRITE; insert into `payments`(`id`,...) values (100000000, ...), ..., (999999999, ...); //поля service_id в списке параметров нет update `payments` set `service_id` = `id`; UNLOCK TABLES; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

but I still get the error:

Query: insert into payments ( id , ...

Error Code: 1062 Duplicate entry '0-0' for key 'index_service_id'

What is the cause of the error? Why does it occur even after disabling index uniqueness checking? Or how to properly disable this check?

UPD

In addition, I would very much like to do this without changing the table schema, if possible.

    2 answers 2

    Documentation about unique_checks :

    Setting this variable to 0 to ignore duplicate keys. Check it out.

    This setting is a recommendation for the storage engine, not a requirement.

    Mysql is a strange thing, but, fortunately, checking unique constraints is as simple as checking foreign keys cannot be disabled. Therefore, use the normal approaches adopted for other databases.

    1. Allow NULL, for example. But on the big table, it’s not up to date, as it is with mysql, whether it is possible to remove the non null constraint without rebuilding the table.

    2. delete a unique index, insert data, create a unique index back. Judging by the permissibility of using LOCK TABLE WRITE, this is not an OLTP load. The time it takes to rebuild an index depends on which part of the data is updated and which part remains.

    3. prepare the necessary data in the temporary table, then insert .. select from the temporary table into the real one.

    The last item is more interesting, not requiring to remove not null just for the sake of writing and no need to recompile the index. The option is simple and fast, lock table is not needed at all. But there is one big difficulty related to the fact that you want to do service_id = id : mysql does not have a regular opportunity to get in advance a list of id. How critical is it?

    You can get out like this hack:

     LOCK TABLES `payments` WRITE; -- получаем на приложение следующее значение AI SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'table_name' AND table_schema = DATABASE( ) ; ALTER TABLE tbl AUTO_INCREMENT=(текущее_значение с прошлого шага + объём необходимых добавленных строк); UNLOCK TABLES;

    Those. we reserve the necessary volume pool ai, which we can use when writing (in this case, the id field must be specified and counted independently)

     insert into `payments`(`id`,`service_id`,`payments_types_id`) values (:ai, :ai, :type), (:ai+1, :ai+1, :type),...;

    In the future, you can suddenly get problems with changing the server parameter, for example, auto_increment_increment . And again table lock where it is not necessary.

    Otherwise, you need to understand the nature of the service_id value and payments_types_id. For example, it may be acceptable for you to use a simple sequence of numbers, for example, negative for the recording time - service_id is not declared as unsigned , and auto_increment in your table will only be positive.

    • Thank you very much! I didn’t understand only about "mysql doesn’t have a regular opportunity to get the id list in advance" ... When inserting the id data, it is also explicitly inserted, insert 'th, and then, with another query, I do update ( service_id = id ) ... - Ksenia
    • Is this about manually inserting a field with auto-increment undesirable? - Ksenia
    • If you always insert an id explicitly, then why is the field declared auto_increment? If it is auto_increment, then yes, inserting values ​​in an explicit way is an unobvious and therefore undesirable action. This phrase is about the fact that, for example, in postgresql, you can call the nextval function and thus reserve how much id is needed for the records before performing the insert and immediately form a correct insert with all the data. And in mysql, you have to hang a table lock and hope for the constancy of the id distribution. - Shallow

    As far as I remember mysql, null in it is not considered to be equal to null. That is, you can assign a null to this field and this will bypass the index uniqueness check. That is, you need

     ALTER TABLE `payments` `id` int(9) unsigned zerofill

    and then do what they wanted.

    • And how does " id int (9) unsigned zerofill" refer to NULL? - Ksenia
    • Well, there is no NOT NULL option, now you can write null there - Smithson
    • The problem is that a lot of records are inserted into the table, so this option probably does not suit me ... - Ksenia