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:
Sets the
SQL_MODEDBMS session variable to the value stored in theSqlModeproperty of the document when performing reverse engineering, forward engineering or synchronization operations.Honors the
SQL_MODEvalues defined inSqlModeso 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_UPDATESoption for the sesson, preventingUPDATEandDELETEstatements from being executed if aWHEREclause 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 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.