A set of visual tools to create, edit, and manage SQL queries, database connections, and objects.

The visual SQL Editor lets you build, edit and run queries, create and edit data, and view and export results. Color syntax highlighting, context sensitive help and auto-complete helps write and debug SQL statements. The integrated EXPLAIN plans provide data to help optimize the your queries.

The following sections describe how to use the visual SQL editor.

SQL Query Window

In this area, you can enter SQL statements directly. The statements entered can be saved to a file or snippet for later use. At any point, you can also execute the statements you have entered.

To save a snippet of code entered into the SQL Query panel, click the Save SQL to Snippets List icon in the Snippets panel, enter a name (optional), and click OK. The snippet can be inserted into the SQL Query panel at any time by double-clicking the snippet in the SQL Snippets panel.

Executing a SELECT query will display the associated result set in the SQL View panel, directly below the SQL Query panel. These cells are editable if MySQL Workbench is able to determine how, as for example they are editable if a Primary or Unique key exists within the result set. If not, MySQL Workbench will display a “read-only” icon at the bottom-right corner of the SQL View panel, and hovering the mouse cursor over this icon will provide a hint as to why it’s not editable.

Note

To quickly enter the name of a table, view, or column, double-click the item in the Schemata Palette. The item name will be inserted into the SQL Query panel.

The SQL Editor has several configurable panels and windows, as described in the screenshot above.

SQL Query Window Toolbar

The toolbar features buttons in two locations, in the main toolbar and within the SQL Editor itself. The SQL Editor buttons are described below.

From left to right, these buttons are:

• Open an SQL Script File: Loads contents from a saved SQL script into the SQL editor.

• Save SQL Script to File: Saves contents from the SQL editor into a file.

• Execute SQL Script: Executes the selected portion of the query, or the entire query if nothing is selected.

• Execute Current SQL script: Execute the statement under the keyboard cursor.

• Explain (All or Selection): Execute the EXPLAIN command on the query under the keyboard cursor.

  A “Results Grid” tab is also displayed when executing an EXPLAIN statement. Clicking it will execute the same query, as if Execute SQL Script was selected.

  Alternatively, the Visual Explain plan is already available for all executed queries. Select Execution Plan from the results tab to view it.

• Stop the query being executed: Halts execution of the currently executing SQL script.

  Note

  The database connection will not be restarted, and open transactions will remain open.

• Toggle whether execution of SQL script should continue after failed statements: If the red “breakpoint” circle is displayed, the script terminates on a statement that fails. If the button is depressed so that the green arrow is displayed, execution continues past the failed code, possibly generating additional result sets. In either case, any error generated from attempting to execute the faulty statement is recorded in the Output tabsheet.

  This behavior can also be set from the SQL Execution user preferences panel.

• Commit: Commits the current transaction.

  Note

  All query tabs in the same connection share the same transactions. To have independent transactions, a new connection must be opened.

• Rollback: Rolls back the current transaction.

  Note

  All query tabs in the same connection share the same transactions. To have independent transactions, a new connection must be opened.

• Toggle Auto-Commit Mode: If selected, each statement will be committed independently.

  Note

  All query tabs in the same connection share the same transactions. To have independent transactions, a new connection must be opened.

  Auto-commit is enabled by default, and this default behavior can be modified (disabled) under the SQL Execution user preferences panel.

• Set Limit for Executed Queries: The default value is 1000, which appends “LIMIT 0, 1000” to SELECT queries.

  The default (1000) can be changed from the SQL Execution user preferences panel.

• Save Snippet: Save the current statement or selection to the active snippet list.

• Beautify SQL: Beautify/reformat the SQL script.

  By default, SQL keywords are changed to UPPER CASE. This functionality can be changed from the SQL Editor user preferences panel.

• Find panel: Show the Find panel for the editor.

• Invisible characters: Toggle display of invisible characters, such as newlines, tabs, spaces.

  A new line is represented as [LF], a space as a single dot (.), and a tab as a right arrow.

• Wrapping: Toggles the wrapping of long lines in the SQL editor window.

Query and Edit Menus

When an SQL Editor tab is selected, the most important items on the main menu bar are the Query and Edit menus.

SQL Query Menu

The Query menu features the following items:

• Execute (All or Selection): Executes all statements in the SQL Query area, or only the selected statements.

• Execute (All or Selection) to Text: Executes all statements in the SQL Query area, or only the selected statements, and displays it in plain text like the standard MySQL command line console.

• Execute Current Statement: Executes the current SQL statement.

• Execute Current Statement (Vertical Text Output): Executes all statements in the SQL Query area, or only the selected statements, and displays it in plain text like the MySQL command line console does vertically (\G).

• Explain Current Statement: Describes the current statement by using the MySQL EXPLAIN statement.

• Visual Explain Current Statement: Visually describes the current statement, based on EXPLAIN information provided by MySQL Server 5.6 and above. MySQL Workbench parses the EXPLAIN (JSON) output from MySQL server 5.6+, and outputs a visual representation.

  For additional information about Visual Explain, see Section 7.3, “Visual Explain Plan” and Section 7.5, “Tutorial: Using Visual Explain to improve query performance”.

• Stop: Stops executing the currently running script.

• Stop Script Execution On Errors: If enabled, MySQL Workbench stops executing the a query if errors are found. It can be enabled/disabled from this menu.

• Limit Rows: By default, the number of returned rows (LIMIT) is 1000. Values defined here affects subsequent statements. The number ranges from 10 to 50000, and “Don’t Limit”.

• Collect Performance Schema Stats: Provides data to the Query Stats result set view, which includes statement specific information about Timing, Rows processed, Temporary tables, Joins per type, Sorting, and Index usage.

• Collect Resultset Field Metadata: Provides data to the Form Editor and Field Types result set views.

• Reconnect to Server: Reconnects to the MySQL server.

• New Tab to Current Server: Creates a duplicate of the current SQL Editor tab.

• Auto-Commit Transactions: Enable to auto-commit transactions.

• Commit Transaction: Commits a database transaction.

• Rollback Transaction: Rolls back a database transaction.

• Commit Result Edits: Commits any changes you have made to the server.

• Discard Result Edits: Discards any changes you have made.

• Export Results: Exports result sets to a file. Selecting this option displays the Export Query Results to File dialog. The dialog enables you to select which result set you wish to export, the file format (CSV, HTML, XML), and the name and location of the output file. Then click Export to export the data.

Edit Menu

The Edit menu features the Format submenu. The Format submenu includes the following menu items:

• Beautify Query: Reformats the query selected in the query tab and lays it out in nicely indented fashion.

• UPCASE Keywords: Converts keywords to uppercase in the currently selected query in the query tab.

• lowercase Keywords: Converts keywords to lowercase in the currently selected query in the query tab.

• Un/Comment Selection: Comments the lines currently selected in the query tab. If the lines are already commented, this operation removes the comments.

• Auto-complete: Triggers the auto-completion wizard. This is enabled (and triggered) by default, and can be disabled with Preferences, SQL Editor, Automatically Start Code Completion. Auto-completion will list functions, keywords, schema names, table names and column names.

Results Window

The results area of the screen shows the results from executed statements. If the script contains multiple statements, a result tab will be generated for each statemented that returned results.

Note

MySQL Workbench handles quoting and escaping for strings entered into the results grid, so adding quotes and proper escaping here is optional.

Note

It is possible to enter a function, or other expression, into a field. Use the prefix \func to prevent MySQL Workbench from escaping quotation marks. For example, for the expression md5('fred'), MySQL Workbench normally would generate the code md5(\'fred\'). To prevent this, enter the expression as \func md5('fred') to ensure that the quoting is not escaped.

The result grid navigation panel offers the following options:

• Reset: Resets all sorted columns.

• Refresh: Refreshes all data by re-executing the original statement.

• Filter Rows: performs a case-insensitive search of all cells. It automatically refreshes, and there is also the refresh button to perform this action manually.

• Edit Current Row: Edit the current row.

• Add New Row: Adds a new empty row, and highlights it in edit mode. Click Apply to execute (and review) the insert row query.

• Delete Selected Rows: Deletes the selected rows. Click Apply execute (and review) the delete query.

• Export: Writes a result set to a CSV, HTML, JSON, SQL INSERT, Excel, XML, or Tab separated file as required.

  Note

  This exports a result set. To export an entire table or schema, see Data Export.

• Import: Import records from an external CSV file.

• Wrap Cell Content: If the contents of a cell exceeds the cell width, then the data will be cut off with an ellipses. This option will instead wrap the contents within the cell, and adjust the cell height accordingly.

  Note

  The “Refresh” button automatically adjusts the column width to match the longest string one of its cells. You may also manually adjust the column width.

Right-clicking on a results grid tab opens the following context-menu:

• Rename Tab: Customize the name (title) of this tab.

• Pin Tab: Pin the results tab to the results grid. Executing additional SQL statements will create new result grid tabs.

• Close Tab: Close this tab.

• Close Other Tabs: Close all tabs except this one.

Context Sensitive Help

Select a keyword or function in your query, and after a delay it shows formatted help information from the MySQL Server (equivalent to using the help command from the command-line MySQL Client).

Output History Panel

The Output is located at the bottom of MySQL Workbench. Its select box includes the Action Output, History Output, and Text Output options.

The Action Output panel displays a summary of the communication between the active MySQL connection in MySQL Workbench and the MySQL server, and can refer to errors or general information. Each message displays the time, action, and server response. This output is useful for troubleshooting scripts.

The History Output panel provides a history of SQL operations carried out in MySQL Workbench for the active MySQL connection. The time and SQL code for each operation is recorded. To view the executed SQL statement, click the time, and the SQL code executed will be displayed in the SQL column.

Table Data Search Panel

Find data across a MySQL connection by using the text search feature on any number of tables and schemas.

From the Schema Tree, choose the tables and/or schemas you want to search, and and then select Search Data Table… from the context menu.

The search options include:

• Search for table fields that: “CONTAINS”, “Search using =”, “Search using LIKE”, “Search using REGEXP”. These search options are case-insensitive.

• Max. matches per table: [100]

• Max. total matches: [1000]

• [ ] Search columns of all types: If checked, non-text column type columns are casted to CHAR to perform the matches, otherwise only text types (CHAR, VARCHAR, and TEXT) are searched. This is unchecked by default.

Tutorial: Adding Data

In the previous section, you created a model, schema, and table. You also forward engineered your model to the live MySQL server. This section uses MySQL Workbench to add data into your MySQL database.

Open a MySQL connection.

1. From the Navigator panel on the left, select the movies table from the dvd_collection schema that we created earlier in this tutorial. Right-click on the movies table and choose Select Rows - Limit 1000 from the context menu.

   Note

   The Navigator panel has both Management and Schemas tabs.

   

2. This displays the query and its associated results grid. The table is empty, and data may be added into the results grid.

   Note

   The movie_id column is set to AUTO_INCREMENT, so values are not needed for this column.

   Input the following data into the movies table:

   title	release_date
   Gone with the Wind	1939-04-17
   The Hound of the Baskervilles	1939-03-31
   The Matrix	1999-06-11
   Above the Law	1988-04-08
   Iron Man 2	2010-05-07

   Note

   Do not modify movie_id column values.

3. Click Apply to apply these changes to the live MySQL server.

4. View the data grid again and observe the generated AUTO_INCREMENT values.

   

5. Optionally, you might confirm the changes by checking an external source, such as the MySQL Command Line Client. To check, enter SELECT * FROM movies; from the MySQL Command Line Client to confirm that the data was entered.

   

6. You can also use MySQL Workbench to perform a similar check. Close the MyFirstConnection tab (or MySQL Workbench) and then open the MyFirstConnection connection from the home page. Execute USE dvd_collection; SELECT * FROM movies; to display the newly entered data.

In this section of the tutorial, you have learned how to add data to your database, and also how to execute SQL statements using MySQL Workbench.

For additional information about the SQL editor, see Section 8.1, “Visual SQL Editor”.

The MySQL Table Editor

The MySQL Table Editor is a used to create and modify tables. .ou can add or modify a table’s columns or indexes, change the engine, add foreign keys, or alter the table’s name.

To access the MySQL Table Editor, right-click on a table name in the Object Viewer and choose ALTER TABLE. This opens a new tab within the main SQL Editor window. You can also access the MySQL Table Editor from an EER Diagram by double-clicking on a table object.

The Main Editor Window

Any number of tables may be edited in the MySQL Table Editor at any one time. Adding another table creates a new tab at the top of the editor. By default, the MySQL Table Editor appears docked at the top of the table editor tab, within the SQL editor..

The MySQL Table Editor is shown on top of the following figure.

The MySQL Table Editor provides a work space that has tabs used to perform these actions:

• Columns: Add or modify columns

• Indexes: Add or modify indexes

• Foreign Keys: Add or modify foreign keys

• Triggers: Add or modify triggers

• Partitioning: Manage partitioning

• Options: Add or modify other options, divided in categories named general, row, storage, and merge

The Columns Tab

Use the Columns tab to display and edit all the column information for a table. With this tab, you can add, drop, and alter columns.

You can also use the Columns tab to change column properties such as name, data type, and default value.

Right-click a row under the Column Name column to open a pop-up menu with the following items:

• Move Up: Move the selected column up.

• Move Down: Move the selected column down.

• Copy: Copies the column for a model.

• Cut: Copies and then deletes the column for a model.

• Paste: Pastes the column. If a column with the same name already exists, then _copy1 is appended to the column name.

• Delete Selected Columns: Select multiple contiguous columns by right-clicking and pressing the Shift key. Use the Control key to select separated columns.

• Refresh: Update all information in the Columns tab.

• Clear Default: Clear the assigned default value.

• Default NULL: Set the column default value to NULL.

• Default 0: Set the column default value to 0.

• Default CURRENT_TIMESTAMP: Available for TIMESTAMP data types.

• Default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP: Available for TIMESTAMP data types.

To add a column, click the Column Name field in an empty row and enter an appropriate value. Select a data type from the Datatype list. Select the column property check boxes as required according to the list of column properties below, and also read the CREATE TABLE documentation for information about what these options mean.

• PK: PRIMARY KEY

• NN: NOT NULL

• UQ: UNIQUE INDEX

• BIN: BINARY

• UN: UNSIGNED

• ZF: ZEROFILL

• AI: AUTO_INCREMENT

To change the name, data type, default value, or comment of a column, double-click the value to edit it.

You can also add column comments to the Column Comment field. It is also possible to set the column collation, using the list in the Column Details panel.

To the left of the column name is an icon that indicates whether the column is a member of the primary key. If the icon is a small key, that column belongs to the primary key, otherwise the icon is a blue diamond or a white diamond. A blue diamond indicates the column has NN set. To add or remove a column from the primary key, double-click the icon. You can also add a primary key by checking the PRIMARY KEY check box in the Column Details section of the table editor.

If you wish to create a composite primary key you can select multiple columns and check the PK check box. However, there is an additional step that is required, you must click the Indexes tab, then in the Index Columns panel you must set the desired order of the primary keys.

Note

When entering default values, in the case of CHAR and VARCHAR data types MySQL Workbench will attempt to automatically add quotation marks, if the user does not start their entry with one. For other data types the user must manage quoting if required, as it will not be handled automatically by MySQL Workbench.

Caution

Care must be taken when entering a default value for ENUM columns because a non-numeric default will not be automatically quoted. You must manually add single quote characters for the default value. Note that MySQL Workbench will not prevent you from entering the default value without the single quotation marks. If a non-numeric default value is entered without quotation marks, this will lead to errors. For example, if the model is reverse engineered, the script will contain unquoted default values for ENUM columns and will fail if an attempt is made to run the script on MySQL Server.

Note

ENUM, BIT, and SET must contain at least one value when entering these data types into MySQL Workbench.

The Indexes Tab

The Indexes tab holds all index information for your table. Use this tab to add, drop, and modify indexes.

Select an index by right-clicking it. The Index Columns section displays information about the selected index.

To add an index, click the last row in the index list. Enter a name for the index and select the index type from the list. Select the column or columns that you wish to index by checking the column name in the Index Columns list. You can remove a column from the index by removing the check mark from the appropriate column.

You can also specify the order of an index by choosing ASC or DESC under the Order column. Create an index prefix by specifying a numeric value under the Length column. You cannot enter a prefix value for fields that have a data type that does not support prefixing.

To drop an index, right-click the row of the index you wish to delete, then select the Delete Selected Indexes menu item.

The Foreign Keys Tab

The Foreign Keys tab is organized in much the same fashion as the Indexes tab and adding or editing a foreign key is similar to adding or editing an index.

To add a foreign key, click the last row in the Foreign Key Name list. Enter a name for the foreign key and select the column or columns that you wish to index by checking the column name in the Column list. You can remove a column from the index by removing the check mark from the appropriate column.

Under Foreign Key Options, choose an action for the update and delete events. The options are:

• RESTRICT

• CASCADE

• SET NULL

• NO ACTION

To drop a foreign key, right-click the row you wish to delete, then select the Delete Selected FKs menu item.

To modify properties of a foreign key, select it and make the desired changes.

The Triggers Tab

The Triggers tab opens a textbox to create or edit existing triggers.

To add a new trigger, click the [+] icon next to the trigger section. To delete a trigger, click the associated [-] icon. These icons become visible by hovering over a trigger or trigger section. Click Apply to commit your changes.

The Partitioning Tab

To enable partitioning for your table, check the Enable Partitioning check box. This enables the partitioning options.

The Partition By pop-up menu displays the types of partitions you can create:

• HASH

• LINEAR HASH

• KEY

• LINEAR KEY

• RANGE

• LIST

Use the Parameters field to define any parameters to be supplied to the partitioning function, such as an integer column value.

Choose the number of partitions from the Partition Count list. To manually configure your partitions, check the Manual check box. This enables entry of values into the partition configuration table. The entries in this table are:

• Partition

• Values

• Data Directory

• Index Directory

• Min Rows

• Max Rows

• Comment

Subpartitioning is also available. For more information about partitioning, see Partitioning.

The Options Tab

The Options tab enables you to set several types of options.

which are grouped into the following sections:

• General Options

• Row Options

• Storage Options

• Merge Table options

The following discussion describes these options in more detail.

General Options Section

In the General Options section, choose a pack keys option. The options are Default, Pack None, and Pack All. You may also encrypt the definition of a table. The AUTO_INCREMENT and delayed key update behaviors apply only to MyISAM tables.

Row Options Section

To set the row format, choose the desired row format from the list. For more information about the different row formats that are available, see MyISAM Table Storage Formats.

These options are:

• Default

• Dynamic

• Fixed

• Compressed

• Redundant

• Compact

When you expect a table to be particularly large, use the Avg. Row, Min. Rows, and Max. Rows options to enable the MySQL server to better accommodate your data. See CREATE TABLE Syntax for more information on how to use these options.

Storage Options Section

The Storage Options section is available only for MyISAM tables. Use it to configure a custom path to the table storage and data files. This can help improve server performance by locating different tables on different hard drives.

Merge Table Options Section

Use the Merge Table Options section to configure MERGE tables. To create a MERGE table, select MERGE as your storage engine and then specify the MyISAM tables you wish to merge in the Union Tables dialog.

You may specify the action the server should take when users attempt to perform INSERT statements on the merge table. You may also select the Merge Method by selecting from the list. For more information about MERGE tables, see The MERGE Storage Engine.

Code Generation Overview

This document provides a quick hands-on introduction to using MySQL Workbench to generate code for later use, for either in or outside of MySQL Workbench.

Generating SQL Statements

MySQL Workbench can be used to generate SQL, most typically as either INSERT statements or SELECT statements.

Below are common methods for generating SQL statements in MySQL Workbench.

Note

All of the MySQL Workbench Export options include the option to export as SQL.

Context-menu options after right-clicking on a schema in the schema view, using the sakila column as an example:

Create Statement

CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET latin1 */;

Name

`sakila`

Context-menu options after right-clicking on a table in the schema view, using the sakila.actor column as an example:

Name (Short)

`actor`

Name (Long)

`sakila`.`actor`

Select All Statement

SELECT `actor`.`actor_id`,
    `actor`.`first_name`,
    `actor`.`last_name`,
    `actor`.`last_update`
FROM `sakila`.`actor`;

Select with References

SET @actor_id_to_select = ;
SELECT film_actor.*
    FROM film_actor, actor
    WHERE `actor`.`actor_id` = `film_actor`.`actor_id`
          AND actor.actor_id = @actor_id_to_select;
SELECT actor.*
    FROM actor
    WHERE actor.actor_id = @actor_id_to_select;

Insert Statement

INSERT INTO `sakila`.`actor`
  (`actor_id`,
  `first_name`,
  `last_name`,
  `last_update`)
VALUES
  (,
  ,
  ,
  );

Update Statement

UPDATE `sakila`.`actor`
SET
`actor_id` = ,
`first_name` = ,
`last_name` = ,
`last_update` =
WHERE `actor_id` = ;

Delete Statement

DELETE FROM `sakila`.`actor`
WHERE ;

Delete with References

-- All objects that reference that row (directly or indirectly)
-- will be deleted when this snippet is executed.
-- To preview the rows to be deleted, use Select Row Dependencies
START TRANSACTION;
-- Provide the values of the primary key of the row to delete.
SET @actor_id_to_delete = ;

DELETE FROM film_actor
    USING film_actor, actor
    WHERE `actor`.`actor_id` = `film_actor`.`actor_id`
          AND actor.actor_id = @actor_id_to_delete;
DELETE FROM actor
    USING actor
    WHERE actor.actor_id = @actor_id_to_delete;
COMMIT;

Create Statement

CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

Context-menu options after right-clicking on a column in the schema view, using the sakila.actor.first_name column as an example:

Name (short)

`first_name`

Name (long)

`actor`.`first_name`

Select Columns Statement

SELECT `first_name` FROM `sakila`.`actor`;

Insert Statement

INSERT INTO `sakila`.`actor`
(`first_name`)
VALUES
();

Update Statement

UPDATE `sakila`.`actor`
SET
`first_name` =
WHERE ;

Context-menu options after right-clicking on a field in the results view, using record #1 in the sakila.actor table as an example:

Copy Rows (with names)

# actor_id, first_name, last_name, last_update
'1', 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33'

Copy Rows (with names, unquoted)

# actor_id, first_name, last_name, last_update
1, PENELOPE, GUINESS, 2006-02-15 04:34:33

Copy Row (tab separated)

1   PENELOPE    GUINESS 2006-02-15 04:34:33

Copy Field

'GUINESS'