Table fields: id (primary key), IP, user, password, create_time, update_time
Index name 1, acc, fields: name, Unique user type, the method BTREE
Index name 2, name, field name, type, Normal method BTREE

INSERT INTO account (
The ` IP `,
The ` user `,
The ` password `,
The create_time,
The update_time
)
VALUES
(
192.168.1.103 ',
'test1'
'm3443'
NOW (),
NOW ()
)
ON the DUPLICATE KEY UPDATE
The ` IP `=VALUES (the ` IP `),
The ` user `=VALUES (the ` user `),
The ` password `=VALUES (the ` password `),
The update_time=NOW ()

I want according to the two Unique IP and user fields, when we have the same IP and user is updated, not just insert the new data.
ON the DUPLICATE KEY UPDATE, my understanding is that ON the DUPLICATE KEY UPDATE the previous command in the presence of UNIQUE field when something goes wrong, execute commands, namely the IP, user, password, update_time updates to the database,
But as a result, create_time update all into this field in recent time, don't know what's the matter, consult everybody under

CodePudding user response:

The problem of table structure?
Show create table account look at the script, it should be set up on the update of the default values

CodePudding user response:

reference 1st floor ZJCXC response:

table structure problem?
Show create table account look at the script, it should be set up on the update of the default

According to your method again
` time ` timestamp NULL DEFAULT NULL ON the UPDATE CURRENT_TIMESTAMP,
Whether can be translated into, timestamp type, default NULL, empty value is automatically updated to the current time??
I thought in navcat design table options can be seen in table structure, unexpectedly and type the command to see this table structure
The, is modified to ` time ` timestamp DEFAULT NULL?
Can't do such a table structure modification navcat? Can only create a new table?

CodePudding user response:

Default null refers to insert if you don't specify values for fields, is set to null
ON the UPDATE CURRENT_TIMESTAMP is batch updates, if does not specify values for fields, updated to CURRENT_TIMESTAMP
Can directly change the table structure, interface didn't try, generally with SQL SQL directly

CodePudding user response:

Create_time don't add the update properties