MySQLWorkbench 编辑表数据是只读的

小开

According to this bug, the issue was fixed in Workbench 5.2.38 for some people and perhaps 5.2.39 for others—can you upgrade to the latest version (5.2.40)?

Alternatively, it is possible to workaround with:

SELECT *,'' FROM my_table

小开

最佳答案

I'm assuming the table has a primary key. First try to run a unlock tables command to see if that fixes it.

If all else fails you can alter the table to create a new primary key column with auto-increment and that should hopefully fix it. Once you're done you should be able to remove the column without any issues.

As always you want to make a backup before altering tables around. :)

Note: MySQL workbench cannot work without a primary key if that's your issue. However if you have a many to many table you can set both columns as primary keys which will let you edit the data.

小开

If you set a default schema for your DB Connection then Select will run in readonly mode until you set explicitly your schema

USE mydb;
SELECT * FROM mytable

this will also run in edit mode:

SELECT * FROM mydb.mytable

(MySql 5.2.42 / MacOsX)

I hope this helps.

小开

Guided by Manitoba's post, I found another solution. As a summary, the solutions are:

With a USE command
```
USE mydb;
SELECT * FROM mytable
```
With an explicit schema prefix:
```
SELECT * FROM mydb.mytable
```
GUI

On Object Browser "SCHEMAS" pane, all database icons are initially not highlighted if you have the same issue. So you can right click on the database icon you wanted to be the default, select "Set as default schema".

小开

if the table does not have primary key or unique non-nullable defined, then MySql workbench could not able to edit the data.

小开

Hovering over the icon "read only" in mysql workbench shows a tooltip that explains why it cannot be edited. In my case it said, only tables with primary keys or unique non-nullable columns can be edited.

小开

1.)You have to make the primary key unique, then you should be able to edit.

right click on you table in the "blue" schemas ->ALTER TABLE, look for your primert key (PK), then just check the check-box, UN, the AI should already be checked. After that just apply and you should be able to edit the table data.

2.)You also need to include the primery key I your select statement

Nr 1 is not really necessary, but a good practice.

小开

If your query has any JOINs, Mysql Workbench will not allow you to alter the table, even if your results are all from a single table.

For example, the following query

SELECT u.* FROM users u JOIN passwords p ON u.id=p.user_id WHERE p.password IS NULL;

will not allow you to edit the results or add rows, even though the results are limited to one table. You must specifically do something like:

SELECT * FROM users WHERE id=1012;

and then you can edit the row and add rows to the table.

小开

In MySQL Workbench you need an INDEX to edit, no need it to be PK (although adding a PK is a solution as well).

You can make a regular INDEX or compound INDEX. That's all MySQL WB needs to fix the Read only thing (I have v. 6.2 with MariaDB v. 10.1.4):

Just right click table, select "Alter table..." then go to "Indexes" tab. In the left pane put a custom name for your index, and in the middle pane checkmark one (make sure the vale will be unique) or more fields (just make sure the combination is unique)

小开

This is the Known limitation in MySQLWorkbench (you can't edit table w/o PK):

To Edit the Table:

Method 1: (method not working in somecases)
right-click on a table within the Object Browser and choose the Edit Table Data option from there.

Method 2:
I would rather suggest you to add Primary Key Instead:

ALTER TABLE `your_table_name` ADD PRIMARY KEY (`column_name`);

and you might want to remove the existing rows first:

Truncate table your_table_name

小开

I was getting the read-only problem even when I was selecting the primary key. I eventually figured out it was a casing problem. Apparently the PK column must be cased the same as defined in the table. using: workbench 6.3 on windows

Read-Only

SELECT leadid,firstname,lastname,datecreated FROM lead;

Allowed edit

SELECT LeadID,firstname,lastname,datecreated FROM lead;

小开

Uncheck the marked check, it will enable the grid edit

小开

MySQL will run in Read-Only mode when you fetch by joining two tables and columns from two tables are included in the result. Then you can't update the values directly.

小开

Yes, I found MySQL also cannot edit result tables. Usually results tables joining other tables don't have primary keys. I heard other suggested put the result table in another table, but the better solution is to use Dbeaver which can edit result tables.

小开

If your table is in read-only mode, checking first is the primary key. If the primary key is not defined, add as the following,

ALTER TABLE scema_here.table_name_here ADD PRIMARY KEY (your primary key here);

Cheers ! :)