MySQL Workbench编辑表数据只读取

时间：2022-02-24 16:24:18

When trying Edit Table Data in MySQL Workbench 5.2.37, its in read only mode.

在MySQL Workbench 5.2.37中尝试编辑表数据时，它处于只读模式。

It is editable only if the table has a primary key.

只有在表有主键时才可编辑。

Is there any fix to deal with table without primary key??

没有主键是否有处理表的方法?

Thanks

谢谢

As one of the suggestion I tried upgrading WB 5.2.40. But still this issue exists.. Could any one Help please..

作为建议之一，我尝试升级wb5.2.40。但是这个问题仍然存在。有谁能帮忙吗

10 个解决方案

#1

44  

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.

注意:MySQL workbench如果没有主键就不能工作，如果这是您的问题的话。但是，如果有许多到多个表，可以将这两列设置为主键，以便编辑数据。

#2

18  

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

如果表没有定义主键或唯一的不可空，那么MySql workbench不能编辑数据。

#3

10  

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

如果为DB连接设置了默认模式，那么Select将以只读模式运行，直到显式地设置模式

USE mydb;
SELECT * FROM mytable

this will also run in edit mode:

这也将在编辑模式下运行:

SELECT * FROM mydb.mytable

(MySql 5.2.42 / MacOsX)

(MySql 5.2.42 / MacOsX)

I hope this helps.

我希望这可以帮助。

#4

4  

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

这是MySQLWorkbench中已知的局限性(不能编辑表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.

方法1:(方法不在somecases中工作)右键单击对象浏览器中的一个表，然后从那里选择Edit table Data选项。

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

#5

2  

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

在MySQL Workbench中，您需要编辑一个索引，不需要它是PK(尽管添加PK也是一种解决方案)。

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):

你可以制作一个常规索引或复合索引。这就是MySQL WB需要修复的唯一内容(我有v6.2和MariaDB v10.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)

只需右键单击table，选择“Alter table…”，然后转到“Indexes”选项卡。在左边的窗格中为您的索引放置一个自定义名称，在中间的窗格中选中一个(确保vale是唯一的)或多个字段(只要确保组合是唯一的)

#6

1  

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)?

根据这个bug，这个问题在Workbench 5.2.38中被修复了，有些人可能是5.2.39，其他人可能是5.2.39，您可以升级到最新的版本(5.2.40)吗?

Alternatively, it is possible to workaround with:

或者，也可以解决以下问题:

SELECT *,'' FROM my_table

#7

1  

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.

在mysql workbench中“只读”图标上方悬停，显示一个工具提示，说明为什么不能编辑它。在我的例子中，只有主键或唯一不可空列的表才能被编辑。

#8

0  

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

在Manitoba的文章指导下，我找到了另一个解决方案。综上所述，解决方案如下:

  1. With a USE command

    使用命令

    USE mydb;
SELECT * FROM mytable

  2. With an explicit schema prefix:

    有明确的模式前缀:

    SELECT * FROM mydb.mytable

  3. GUI

    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".

    在对象浏览器“模式”窗格中，如果您有相同的问题，所有的数据库图标都不会被高亮显示。所以你可以右键点击你想要的数据库图标，选择“设置为默认模式”。

#9

0  

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

1)。您必须使主键唯一，然后您应该能够编辑。

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.

在“蓝色”模式下右键单击您的表——>ALTER table，查找您的primert密钥(PK)，然后选中复选框UN, AI应该已经被选中。然后应用，您应该能够编辑表数据。

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

2)。您还需要包含primery键I您的select语句

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

Nr 1不是真正需要的，而是一个很好的练习。

#10

0  

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.

如果您的查询有任何连接，Mysql工作台将不允许您更改表，即使您的结果都来自单个表。

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.

然后可以编辑行并向表中添加行。

#1

44  

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.

注意:MySQL workbench如果没有主键就不能工作，如果这是您的问题的话。但是，如果有许多到多个表，可以将这两列设置为主键，以便编辑数据。

#2

18  

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

如果表没有定义主键或唯一的不可空，那么MySql workbench不能编辑数据。

#3

10  

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

如果为DB连接设置了默认模式，那么Select将以只读模式运行，直到显式地设置模式

USE mydb;
SELECT * FROM mytable

this will also run in edit mode:

这也将在编辑模式下运行:

SELECT * FROM mydb.mytable

(MySql 5.2.42 / MacOsX)

(MySql 5.2.42 / MacOsX)

I hope this helps.

我希望这可以帮助。

#4

4  

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

这是MySQLWorkbench中已知的局限性(不能编辑表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.

方法1:(方法不在somecases中工作)右键单击对象浏览器中的一个表，然后从那里选择Edit table Data选项。

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

#5

2  

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

在MySQL Workbench中，您需要编辑一个索引，不需要它是PK(尽管添加PK也是一种解决方案)。

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):

你可以制作一个常规索引或复合索引。这就是MySQL WB需要修复的唯一内容(我有v6.2和MariaDB v10.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)

只需右键单击table，选择“Alter table…”，然后转到“Indexes”选项卡。在左边的窗格中为您的索引放置一个自定义名称，在中间的窗格中选中一个(确保vale是唯一的)或多个字段(只要确保组合是唯一的)

#6

1  

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)?

根据这个bug，这个问题在Workbench 5.2.38中被修复了，有些人可能是5.2.39，其他人可能是5.2.39，您可以升级到最新的版本(5.2.40)吗?

Alternatively, it is possible to workaround with:

或者，也可以解决以下问题:

SELECT *,'' FROM my_table

#7

1  

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.

在mysql workbench中“只读”图标上方悬停，显示一个工具提示，说明为什么不能编辑它。在我的例子中，只有主键或唯一不可空列的表才能被编辑。

#8

0  

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

在Manitoba的文章指导下，我找到了另一个解决方案。综上所述，解决方案如下:

  1. With a USE command

    使用命令

    USE mydb;
SELECT * FROM mytable

  2. With an explicit schema prefix:

    有明确的模式前缀:

    SELECT * FROM mydb.mytable

  3. GUI

    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".

    在对象浏览器“模式”窗格中，如果您有相同的问题，所有的数据库图标都不会被高亮显示。所以你可以右键点击你想要的数据库图标，选择“设置为默认模式”。

#9

0  

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

1)。您必须使主键唯一，然后您应该能够编辑。

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.

在“蓝色”模式下右键单击您的表——>ALTER table，查找您的primert密钥(PK)，然后选中复选框UN, AI应该已经被选中。然后应用，您应该能够编辑表数据。

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

2)。您还需要包含primery键I您的select语句

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

Nr 1不是真正需要的，而是一个很好的练习。

#10

0  

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.

如果您的查询有任何连接，Mysql工作台将不允许您更改表，即使您的结果都来自单个表。

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.

然后可以编辑行并向表中添加行。

标签：

相关文章