Correct request

Question

Continuation of this topic ! There is a table:

CREATE TABLE IF NOT EXISTS `TABLE_KITS_LOG` ( `FIELD_SERVER_ID` varchar(64) COLLATE utf8_unicode_ci, `FIELD_PLAYER` varchar(64) COLLATE utf8_unicode_ci, `FIELD_KIT_NAME` varchar(64) COLLATE utf8_unicode_ci, `FIELD_TIMESTAMP` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

For her, there is an index:

 CREATE unique index TABLE_KITS_LOG_INDEX on TABLE_KITS_LOG( FIELD_SERVER_ID, FIELD_PLAYER, FIELD_KIT_NAME);

For example, in the table there is a trace record:

 INSERT INTO `TABLE_KITS_LOG` (FIELD_SERVER_ID, FIELD_PLAYER, FIELD_KIT_NAME) VALUES ("rpg", "name", "deluxe"); INSERT INTO `TABLE_KITS_LOG` (FIELD_SERVER_ID, FIELD_PLAYER, FIELD_KIT_NAME) VALUES ("rpg", "name", "mods"); INSERT INTO `TABLE_KITS_LOG` (FIELD_SERVER_ID, FIELD_PLAYER, FIELD_KIT_NAME) VALUES ("rpg", "name", "vip"); INSERT INTO `TABLE_KITS_LOG` (FIELD_SERVER_ID, FIELD_PLAYER, FIELD_KIT_NAME) VALUES ("classic", "name", "deluxe"); INSERT INTO `TABLE_KITS_LOG` (FIELD_SERVER_ID, FIELD_PLAYER, FIELD_KIT_NAME) VALUES ("classic", "name", "mods"); INSERT INTO `TABLE_KITS_LOG` (FIELD_SERVER_ID, FIELD_PLAYER, FIELD_KIT_NAME) VALUES ("classic", "name", "vip");

Since the change only requires the FIELD_TIMESTAMP field FIELD_TIMESTAMP I try to make a query that, using a unique index and table settings, will update this field itself, because when I create a table, I specify the default value and value when updating CURRENT_TIMESTAMP .

Since I indicated that when the record is updated, the FIELD_TIMESTAMP column should update itself, I do not know what to write after the KEY UPDATE , in the version that the error is written below: Error Code: 1136. Column count doesn't match value count at row 1 . It should be noted that if there is no record, then it needs to be inserted, and if there is, update the time.

Attempt to make the necessary request:

 INSERT INTO `TABLE_KITS_LOG` (FIELD_SERVER_ID, FIELD_PLAYER, FIELD_KIT_NAME, FIELD_TIMESTAMP) VALUES ("rpg", "name", "mods") ON DUPLICATE KEY UPDATE FIELD_TIMESTAMP = CURRENT_TIMESTAMP;

@Akina help

Well, type INSERT INTO table_kits_log (field_server_id, field_player, field_kit_name) VALUES ("rpg", "name", "mods") ON DUPLICATE KEY UPDATE field_kit_name=VALUES(field_kit_name);
Um, yesterday there was a conversation that uniqueness is formed using an index.
Why is field_kit_name=VALUES(field_kit_name) specified in this case?

Accepted Answer · 2017-12-20T14:15:06

You need to completely form the insert, specifying all the values. And in update, really, you need to specify only fields that are not included in the unique key

 INSERT INTO `TABLE_KITS_LOG` (FIELD_SERVER_ID, FIELD_PLAYER, FIELD_KIT_NAME, FIELD_TIMESTAMP) VALUES ("rpg", "name", "mods", CURRENT_TIMESTAMP) ON DUPLICATE KEY UPDATE FIELD_TIMESTAMP = CURRENT_TIMESTAMP;

the request will find that a field with a set of components unique key exists and in this case will change the timestap. But the request must be given all the values, in case there is no such field. Moreover, these values may be different. The classic version is the INSERT INTO table (id, count) ($id, 0) ON DUPLICATE KEY UPDATE count = count+1 counter INSERT INTO table (id, count) ($id, 0) ON DUPLICATE KEY UPDATE count = count+1

Thanks, the query works, only questions: 1) Why did I write UPDATE for the field with FIELD_TIMESTAMP when creating FIELD_TIMESTAMP .
2) The program (MySQL Workbench), in which I check this whole thing after fulfilling your request, said that 2 rows were touched, but in fact the entry changed in 1 place and where needed, but then why is it written that 2 rows are affected?
the request will find that a field with a set of components unique key exists and in this case will change the timestap.
But the request must be given all the values, in case there is no such field.
I'll try to do it in the usual MyPhPAdmin, accomplish your goal.

Correct request

1 answer 1

More articles: