MySQL 错误代码：1175 在 MySQL Workbench 中的 UPDATE 期间

MySQL error code: 1175 during UPDATE in MySQL Workbench

提问by Jury A

I'm trying to update the column visitedto give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command:

我正在尝试更新该列visited以赋予其值 1。我使用 MySQL 工作台，并从工作台内部在 SQL 编辑器中编写语句。我正在编写以下命令：

UPDATE tablename SET columnname=1;

It gives me the following error:

它给了我以下错误：

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option ....

您正在使用安全更新模式，并且尝试更新没有使用 KEY 列的 WHERE 的表要禁用安全模式，请切换选项 ....

I followed the instructions, and I unchecked the safe updateoption from the Editmenu then Preferencesthen SQL Editor. The same error still appear & I'm not able to update this value. Please, tell me what is wrong?

我跟着指示，我不加以制止，safe update从选项Edit菜单，然后Preferences再SQL Editor。仍然出现相同的错误，我无法更新此值。请告诉我有什么问题？

采纳答案by Jury A

I found the answer. The problem was that I have to precede the table name with the schema name. i.e, the command should be:

我找到了答案。问题是我必须在表名之前加上模式名。即，命令应该是：

UPDATE schemaname.tablename SET columnname=1;

Thanks all.

谢谢大家。

回答by Habibillah

It looks like your MySql session has the safe-updates optionset. This means that you can't update or delete records without specifying a key (ex. primary key) in the where clause.

看起来您的 MySql 会话设置了安全更新选项。这意味着您不能primary key在 where 子句中不指定键（例如）的情况下更新或删除记录。

Try:

尝试：

SET SQL_SAFE_UPDATES = 0;

Or you can modify your query to follow the rule (use primary keyin where clause).

或者您可以修改您的查询以遵循规则（primary key在 中使用where clause）。

回答by Ripon Al Wasim

Follow the following steps before executing the UPDATE command: In MySQL Workbench

在执行 UPDATE 命令之前，请按照以下步骤操作： 在 MySQL Workbench 中

1. Go to Edit--> Preferences
2. Click "SQL Editor"tab and uncheck"Safe Updates" check box
3. Query--> Reconnect to Server// logout and then login
4. Now execute your SQL query

1. 去Edit-->Preferences
2. 单击"SQL Editor"选项卡和uncheck“安全更新”check box
3. Query--> Reconnect to Server// 注销然后登录
4. 现在执行您的 SQL 查询

p.s., No need to restart the MySQL daemon!

ps，无需重启MySQL守护进程！

回答by user2531028

SET SQL_SAFE_UPDATES=0;
UPDATE tablename SET columnname=1;
SET SQL_SAFE_UPDATES=1;

回答by Rudy De Volder

No need to set SQL_SAFE_UPDATES to 0, I would really discourage it to do it that way. SAFE_UPDATES is by default on for a REASON. You can drive a car without safety belts and other things if you know what I mean ;) Just add in the WHERE clause a KEY-value that matches everything like a primary-key comparing to 0, so instead of writing:

无需将 SQL_SAFE_UPDATES 设置为 0，我真的不鼓励它这样做。SAFE_UPDATES 默认为一个 REASON。如果您知道我的意思，您可以在没有安全带和其他东西的情况下驾驶汽车；) 只需在 WHERE 子句中添加一个 KEY 值，该值匹配与 0 相比的主键之类的所有内容，因此不要写：

UPDATE customers SET countryCode = 'USA'
    WHERE country = 'USA';               -- which gives the error, you just write:

UPDATE customers SET countryCode = 'USA'
    WHERE (country = 'USA' AND customerNumber <> 0); -- Because customerNumber is a primary key you got no error 1175 any more.

Now you can be assured every record is (ALWAYS) updated as you expect.

现在您可以放心，每条记录都（始终）按您的预期更新。

回答by Quagmire12

All that's needed is: Start a new query and run:

所需要的只是：启动一个新查询并运行：

SET SQL_SAFE_UPDATES = 0;

Then: Run the query that you were trying to run that wasn't previously working.

然后： 运行您尝试运行但以前不起作用的查询。

回答by Carlos Henrique Moreira Dos S.

SET SQL_SAFE_UPDATES = 0;

SET SQL_SAFE_UPDATES = 0;

your code SQL here

您的代码 SQL 在这里

SET SQL_SAFE_UPDATES = 1;

SET SQL_SAFE_UPDATES = 1;

回答by Tính Ng? Quang

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

错误代码：1175。您正在使用安全更新模式，并且您尝试更新没有使用 KEY 列的 WHERE 的表要禁用安全模式，请切换首选项 -> SQL 编辑器中的选项并重新连接。

Turn OFF "Safe Update Mode" temporary

暂时关闭“安全更新模式”

SET SQL_SAFE_UPDATES = 0;
UPDATE options SET title= 'kiemvieclam24h' WHERE url = 'http://kiemvieclam24h.net';
SET SQL_SAFE_UPDATES = 1;

Turn OFF "Safe Update Mode" forever

永远关闭“安全更新模式”

Mysql workbench 8.0:

Mysql 工作台 8.0：

MySQL Workbench => [ Edit ] => [ Preferences ] -> [ SQL Editor ] -> Uncheck "Safe Updates"

Old version can:

旧版本可以：

MySQL Workbench => [Edit] => [Preferences] => [SQL Queries]

回答by andrew

1. Preferences...
2. "Safe Updates"...
3. Restart server

1. 喜好...
2. “安全更新”...
3. 重启服务器

回答by Balu

SET SQL_SAFE_UPDATES=0;

OR

或者

Go toEdit --> Preferences

去Edit --> Preferences

ClickSQL Queriestab and uncheckSafe Updatescheck box

单击SQL Queries选项卡并取消Safe Updates选中复选框

Query --> Reconnect to Server

Query --> Reconnect to Server

Now execute your sql query

现在执行你的 sql 查询

回答by Tomislav

If you are in a safe mode, you need to provide id in where clause. So something like this should work!

如果您处于安全模式，则需要在 where 子句中提供 id。所以这样的事情应该有效！

UPDATE tablename SET columnname=1 where id>0