MySQL Extensions and Deviations
This section I am going to cover material relating to the extensions and deviations of MySQL, an administrator should have the basic knowledge of the following even if he is not going to be developing
MySQL does not completely follow the SQL standard (ANSI/ISO SQL:2003 standard) and has it's own extensions and deviations. The reason for the extensions is to make the system easy to use.
We start with some of the rules of the SQL language which relate to the following
MySQL has a clever extension that manages portability, you can use the MySQL version as part of a comment, it will only be parsed if the mysqld server is the appropriate version, otherwise it will left as a comment and ignored by the database.
single line comment | -- this is a single line comment |
multi-line comment | /* this is a multi-line comment */ |
parse the comment line | ## Note the bang character /*! show databases */ |
parse the comment line for a specific version | ## the below only runs on MySQL 4.1.1 or higher /*!40101 show databases */ first digit = major version second & third digit = minor version last two digits = revision number |
Like all databases SQL reserved words are written in uppercase (SELECT, FROM, NULL, etc), however they are all case-insensitive so SELECT, Select and select are all the same, mysqld in general is case-insentive with respect to names of fields, indexes, stored routines and events, However information is stored on the filesystem which is case-sensitive in the Unix world but not in the windows world in which case the following maybe affected
You can change the default behavior by using the global system variable lower_case_table_names
lower_case_table_names | when set to 0, table and database names are stored using the case specified in the create statement, in queries table names, table aliases and database names are case-sensitive, this is the default on unix systems. when set to 1, table and database names are stored using lowercase, in queries table names, table aliases and database names are case-insensitive they are converted into lowercase by mysqld, this is the default on windows systems. when set to 2, table and database names are stored using case specified in the create statement apart from the Innodb tables names which are stored in lowercase , in queries table names, table aliases and database names are case-insensitive they are converted into lowercase by mysqld, this is the default on Mac OS X systems. |
The escape character is the backslash (\) and as such an escape sequence is a backslash followed by one character for example \P and \u these are then interpreted by mysqld, there are a number of escape sequences that are independent of the mysql commands
escape sequences |
|
Identifiers are names of databases, tables, views, fields, indexes, tablespaces, stored routines, triggers, events, servers, log file groups and aliases, they are limited to to 64 characters except for aliases which are limited to 255 characters, you must not end a identifier with a space. You can use reserve words, numbers and punctuation but they must be surrounded by double quotes, however I try never to use the above as it always causes problems in the end, keep it simple and don't try to be too clever by using fancy names, to be honest this is pretty much the same with all other databases.
MySQL has the normal dot notation which can be used to specify a database when referring to a table
dot notation | ## here I specify the database as information_schema and the table as tables select * from information_schema.tables; |
Time zones can be a complex subject and can become confusing, when mysqld starts it determines the time zone of the operating system and sets the system_time_zone system variable accordingly, by default it sets the value of time_zone to SYSTEM, which means that it operates using the time zone in system_time_zone.
Fields with a timestamp value are converted to UTC and stored, when retrieved hey are converted to the time_zone value. Date, Time and Datetime fields are not converted and stored as UTC.
MySQL supports may different character sets and collations. A character set is the set of available chracaters that can be used, similar to the alphabet. The default character set is Latin1 which includes all the character of the Latin languages.
A collation specifies the lexical sort order for example the english alphabet would be "a, b, c, etc" but a spainish alphabet would be "a, b, c, ch, etc", they can also sort in uper and lower case. A binary collation is a collation that is case-senitive, these usually end with a _bin for an example ascii_bin. The sort order in a binary collation is determined by the numeric representation of the character. Each character set has one collation, and each collation is assiocated with exactly one character set, currently there are 39 character set and 197 collations.
The different levels to which the character set and collations can be set are
Server | The system variables character_set_server and collation_server specify the default character set and collation for the database when you do not use the character set or collation clauses with the create database command. |
Database | The system variables character_set_server and collation_server specify the default character set and collation for the current database, these are set with the character set and collation clauses of the create database and alter database commands. The character set and collation are used by load data infile and specify the default character set and collation for a table when create table command is used. |
Table | A create table or alter table <tablename> add column command can use a character set or collation clause, which will set for a field with no character set or collation specified. |
Field | You can sepcify a character set and collation for fields that are data type, char, varchar, tinytext, text, meduimtext or longtext. |
String | You can set a character set or collation for a string sent via the current connection, for example a string as the one in "select hi" will be returned with the character set and collation specified by the character_set_connection and collation_connection system variables. |
There is a lot more to character sets and collation and thus I point you to the MySQL documentation.
MySQL has tried to keep to the standard ODBC SQL standard and the ANSI standard, however like all other databases MySQL has its own set of features that differs from other databases and the standards. Here is a table listing the major differences in MySQL
Storage Engine | Each table is an instantiation of a storage engine, different tables can have different storage engines, different storage engines function differently with regard to erformance, ACID compliance, supported features and more. |
Errors | MySQL makes attempts to make sense of what should throw an error, by default mysqld will truncate invalid data that its too large for a data type, it implicity converts data and more. The sql_mode server variable can be set to change this type of behavior. |
String comparison | Strings are compared in the order determined by the collation, unless you use the binary() function using a cast. |
Data Types | see Data Types sections |
Index Types | see Index Types sections |
Foreign key constraints | MySQL accepts foreign keys constraints in table definitions but only tables using transactional storage engines (InnoDB and falcon), all other storage engines will disregard foreign key constraints without producing an error. You can turn off foreign key checking by using the system variable foreign_key_checks (0=off, 1=on) |
String | There are a number of string related differences
|
Privileges and Permissions | MySQL uses the standard grant and revoke for privileges and permissions, here are some differences to be aware of
|
Transaction management | You can only use transactions in the InnoDB and Falcon storage engines, you start a transaction using the start transaction command, there are no options to this command. The transaction modes are
|
Check Constraints | MySQL does not support check constraints other than those implemented by specifying data types, foreign key constraints and unique key constraints. You can create a check contraint but it will be ignored by all storage engines. |
Upsert statements | MySQL does not support upsert statements with the standard SQL merge statement, it has limited support when using with the on duplicate key update clause to an insert statement. |
There are a number of extensions, options, parameters and general awareness regarding MySQL, for example the show command makes life easier than having to query the information_schema.
I have broken them down into subject categories
Aliases | these are the additional aliases extensions
|
Alter table | the alter table pretty much does all that create table does, you can perform some commands while the table is online and some when the table is offline Online
Offline
|
Create | You can use the if not exists extension, which specifies a warning instead of an error should the mysqld not complete the command. |
DML | There are also extensions for DML (Data Manipulation Language - insert, replace, update, delete)
|
Drop | Drop has a similar command to create a if exists extension, in other words drop if the object exists |
Limit | We mentioned limit above, you can use it to work on a subset of matching rows, and it takes one or two arguements, if one argument is used this it is the number of rows to constrain the output to. If twp arguments are used the first is the offset and the second is the number of rows to limit |
Select | The select statement has a number of options
|
Server Maintenance | There are a number of server maintenance commands that you can use as an administrator, I will be using these thoughout the whole of the MySQL section
|
Set | the set extension is used to assign values to variables, you can use the following syntax set [session|global] @varname:=value |
Show | The show extension can obtain metadata information from the information_schema database, you have already seen examples of this in the architecture section, here is a list of the rest I will only comment to the command is not to obvious
|
Table | There are a number of options to add additional features to table
|
Table Maintenance | Indexes need maintenance for time to time, there are a number of commands that can help
See Table Maintenance for more details |
Transactional | The list below, all perform a implicit commit without your approval and without informing you, however they are not performed if no changed actually happened
|