Several exception records for MySQL update

>>error code:1045. Access denied for user ' test ' @ '% ' (using Password:yes)

This problem is encountered when using MySQL's select * into outfile '/tmp/rs.txt ' from tb_name to export the results.

Although the current user has full permissions, file permissions need to be assigned separately, using the root user to execute:

Grant file on *. * to [email protected];

>>error code:1093. You can ' t specify target table ' mytable ' for update in FROM clause

When using an UPDATE or DELETE statement, a subquery that is added to the where condition is the result of the query.
The table can then be nested another layer, that is, "(SELECT * from table) TT", to obtain a temporary result set,
You can do this on the result set.

Delete from mytable where mytable.id not in (select Tt.id from (SELECT * from MyTable) TT where TT.SITEID=22);

>>error code:1175. You is using Safe update mode and you tried to update a table without a WHERE is uses a KEY column to disable safe mode , Toggle the option in Preferences, SQL Editor and reconnect.

The workaround is to turn off Safe mode:

SET sql_safe_updates = 0;

Note If you are using MySQL Workbench, you also need to configure the software preferences.
Because the default security setting for MySQL Workbench is that the table cannot be updated in bulk.
This error is prompted when the SQL statement to be executed is a batch update or delete.
Here's how to fix it:
Open the Workbench menu [edit]->[preferences ...]
Switch to the [SQL Editor] Page
Remove the checkmark before [forbid UPDATE and DELETE statements without a WHERE clause (safe updates)]
Click the [OK] button

>>mysql using the current time when inserting

The now () function returns the current datetime with ' Yyyy-mm-dd HH:MM:SS ' and can be stored directly in the DateTime field.
Curdate () returns today's date in the format ' Yyyy-mm-dd ', which can be stored directly in the Date field.
Curtime () returns the current time in the format ' HH:MM:SS ', which can be stored directly in the Date field.

INSERT into table (ID, time) VALUES (' 1 ', now ())

>>error code:1100. Table ' mytable ' is not locked with LOCK TABLES

I executed it before inserting it.

LOCK TABLES ' mytable ' WRITE;

To re-unlock:

UNLOCK TABLES;

　　

Several exception records for MySQL update