The SQL Editor Tab

This section provides configuration options that affect the SQL Editor functionality in MySQL Workbench.

There are three main groups of parameters that can be set here:

  • SQL properties

  • Query Editor

  • Query Results

SQL Properties

SQL properties that can be set include the SQL_MODE, case sensitivity of identifiers, and the SQL delimiter used (by default this is $$).

The document property SqlMode defines SQL_MODE for all operations affecting SQL parsing at the document scope. The purpose of this option is to preserve the consistency of SQL statements within the document.

The property has the following function:

  1. Sets the SQL_MODE DBMS session variable to the value stored in the SqlMode property of the document when performing reverse engineering, forward engineering or synchronization operations.

  2. Honors the SQL_MODE values defined in SqlMode so that SQL parsing is correct.

Only a subset of all possible SQL_MODE values affect the MySQL Workbench SQL parser. These values are: ANSI_QUOTES, HIGH_NOT_PRECEDENCE, IGNORE_SPACE, NO_BACKSLASH_ESCAPES, PIPES_AS_CONCAT. Other values do not affect the MySQL Workbench SQL parser and are ignored.

If the value of SqlMode is not set then the default value of the SQL_MODE session variable defined by the server stays unchanged during operations with the server. However, the MySQL Workbench SQL parser will behave as if SQL_MODE is also not set. This may potentially lead to inconsistencies in SQL syntax stored in the document. If you choose to not set the SqlMode property, ensure that the default SQL_MODE variable defined by the server does not contain any values from the following list: ANSI_QUOTES, HIGH_NOT_PRECEDENCE, IGNORE_SPACE, NO_BACKSLASH_ESCAPES, PIPES_AS_CONCAT.

The SqlMode property is defined in two locations: globally and at document scope. Every document upon its creation copies the value of the global property into the property defined for the document. The property value defined at document scope always has higher priority over the one defined globally.

Query Editor

The query editor properties that can be set include the following:

  • Show Live Schema Overview - This option allows a simplification of the user interface by removing the Overview tab from the SQL Editor. This is also extremely useful if schemata have a large number of tables, or there is a large number of schemata in a model. In each of these cases load times would be greatly increased as the tables and schemata are enumerated and drawn.

  • Show Schema Contents in Schema Tree - enumerating, populating and drawing large numbers of items can significantly increase loading times. For this reason this facility can be switched off for models containing large numbers of schemata and tables.

  • Show Metadata Schemata - by default metadata schemata are not displayed. If required to view, for example to troubleshoot or check metadata information, they can be displayed by selecting this option.

  • Continue on SQL Script Error - should an error occur while executing a script, this option will allow you to continue executing the remainder of the script.

  • Forbid UPDATE and DELETE statements without a WHERE clause - this option enables the SQL_SAFE_UPDATES option for the sesson, preventing UPDATE and DELETE statements from being executed if a WHERE clause is not present. This can avoid potentially dangerous situations where a command could accidentally update or delete all rows in a table.

  • Max syntax error count - large complex scripts can contain many errors. Further, a syntax error early on can lead to many subsequent syntax errors. For these reasons it is possible to limit the number of errors displayed using this option, the default being 100 error messages.

  • Progress status update interval - When executing long running queries over a slow connection you would need to increase this value, to prevent excess load on the connection.

  • DBMS connection keep-alive interval - When executing long running queries over a slow connection you would need to increase this value to prevent the connection being lost.

Query Results

  • Limit Rows - queries can sometimes result in an excessive number of rows being returned as a result. This can heavily load the connection, and take time to display in MySQL Workbench. To prevent this you can set a more moderate value here.

  • Limit Rows Count - specify the maximum number of result rows to return.

  • Max. Field Value Length to Display - to avoid display problems due to excessive field length, it is possible to set the maximum field length to display (in bytes).

  • Treat BINARY/VARBINARY as non-binary character string - Binary byte string values are not displayed by default in the results grid, but are instead marked as BLOB values. These can then be viewed or edited with the BLOB editor. Non-binary character string values are displayed in the results grid, and can be edited in the grid cell or using the BLOB editor. Note that if this option is turned on, data truncation may result. This is because binary byte string values may contain null bytes as part of their valid data. For non-binary character strings a null byte terminates the string.

  • Enable Data Changes Commit Wizard - In the SQL Editor, when editing table data and then clicking the Applying changes to data button, a wizard is launched to step you through applying you changes. This gives you a chance to review the SQL that will be applied to the live server to make the requested changes. If the option is deselected, then the changes will simply be applied to the server, without the wizard being displayed, and without a chance to review the changes that will be made.

Copyright © 2010-2023 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout