Compatibility between MySQL and standards (1)

MySQL contains some extensions that may not be found in other sqldatabases. Note that if you use them, your code will not be compatible with other SQL servers. In some cases, you can write code that includes MySQL extension, but it is still portable. Use /*! . In this case, MySQL performs lexical analysis and runs the code in the annotation, as if it is any other MySQL statement, but other SQL servers will ignore the extension. For example:
SELECT /*! STRAIGHT_JOIN */col_name FROM table1, table2 WHERE...
If you are in '! 'And then add a version number. This syntax will only be executed when the MySQL version is equal to or later than the version number used:
CREATE /*! 32302 TEMPORARY */TABLE (a int );
The above means that if you have 3.23.02 or an update, MySQL will use the TEMPORARY keyword.
MySQL extensions are listed below:

The field types include MEDIUMINT, SET, ENUM, and BLOB and TEXT.
Field attributes: AUTO_INCREMENT, BINARY, UNSIGNED, and ZEROFILL.

By default, all string comparisons are case-insensitive and are determined by the current character set (default: ISO-8859-1 Latin1. If you do not like this, you should use the BINARY Attribute or use the BINARY force character to declare the column, which leads to sorting based on the ASCII sequence of the MySQL server host.

MySQL maps each database to a directory under the MySQL data directory, and maps the database table to the database file name under the database directory. This has two meanings:
The Database Name and table name in MySQL are case-sensitive in the operating system with case-sensitive file names (like most Unix systems. If you have difficulties remembering the table name, you can accept a consistent convention, such as creating a database and a table with a lowercase name.
Databases, tables, indexes, columns, or aliases can start with numbers (but cannot be composed of only numbers ).

You can use standard system commands to back up, rename, move, delete, and copy tables. For example, rename a table and rename the ". MYD", ". MYI", and ". frm" files as corresponding tables.

In SQL statements, you can use the db_name.tbl_name syntax to access tables in different databases. Some SQL servers provide the same functions but call them the User space ). MySQL does not support tablespaces similar to those IN create table ralph. my_table... IN my_tablespace.

LIKE is allowed in numeric columns.
Use into outfile and STRAIGHT_JOIN in a SELECT statement. See 7.12 SELECT syntax.
In a SELECT statement, SELECT SQL _SMALL_RESULT.
Explain select to get the description of how to join the table.
Use an INDEX, an INDEX on the field prefix, and an INDEX or KEY in a create table statement. See 7.7 create table syntax.
Create table uses TEMPORARY or if not exists.
Use COUNT (DISTINCT list), where "list" exceeds one element.
Use CHANGE col_name, DROP col_name, or drop index in an alter table statement. See 7.8 alter table syntax.
Use IGNORE in an alter table statement.
Use multiple ADD, ALTER, DROP, or CHANGE clauses in an alter table statement.
Use the drop table with the keyword if exists.
You can discard multiple tables with a single drop table statement.
The LIMIT clause of the DELETE statement.
The DELAYED clause of the INSERT and REPLACE statements.
The LOW_PRIORITY clause of INSERT, REPLACE, DELETE, and UPDATE statements.
Use load data infile. In most cases, this syntax is compatible with Oracle's load data infile. See the 7.16 load data infile syntax.
Optimize table statement ..
SHOW statement. See the 7.21 SHOW syntax (to obtain information about tables and columns ).
A string can be surrounded by "" or "", not just "".
Use the Escape Character.
Set option statement. See the 7.25 set option syntax.
You do not need to name all selected columns in the group by section. This provides better performance for some very specific situations, rather than general queries.
MySQL supports aliases for many functions to help others in the SQL environment. For example, all string functions support ansi SQL syntax and ODBC syntax.

MySQL understands | AND & AND means logical or and, just like in C programming language. In MySQL, | and or are synonyms, AND & AND are synonyms. Because of this good syntax, MySQL does not support ansi SQL for String concatenation | Operator; instead, CONCAT () is used because CONCAT () accepts any number of parameters, it is easy to convert the | Operator to MySQL.
Create database or drop database. See 7.5 create database syntax.
The % operator is a synonym for MOD (), that is, N % M is equivalent to MOD (N, M ). % Supports C programmers and is compatible with PostgreSQL.
=, <>, <=, <, >=, >,<, >>, <=>, AND, the or like operator can be placed on the left of the SELECT statement FROM for comparison columns. For example:
Mysql> SELECT col1 = 1 AND col2 = 2 FROM tbl_name;
LAST_INSERT_ID () function. See section 4.29 mysql_insert_id ().
The extended regular expression operators REGEXP and not regexp.
CONCAT () or CHAR () has one or more parameters. In MySQL, these functions can take any number of parameters .)
BIT_COUNT (), CASE, ELT (), FROM_DAYS (), FORMAT (), IF (), PASSWORD (), ENCRYPT (), md5 (), ENCODE (), DECODE (), PERIOD_ADD (), PERIOD_DIFF (), TO_DAYS (), or WEEKDAY () function.
Use TRIM () to refresh the substring. Ansi SQL only supports deletion of a single character.
Group by functions include STD (), BIT_OR (), and BIT_AND ().
REPLACE instead of DELETE + INSERT. See 7.15 REPLACE syntax.
FLUSH flush_option statement.
In a statement, use: = to set the possibility of variables:
SELECT @ a: = SUM (total), @ B = COUNT (*), @ a/@ B AS avg FROM test_table;
SELECT @ t1: = (@ t2: = 1) + @ t3: = 4, @ t1, @ t2, @ t3;
Run MySQL in ANSI Mode

If you use the -- ansi option to start mysqld, the following behavior changes of MySQL.
| It is the union of strings rather than OR.
There can be any number of spaces between a function name and. This also makes all functional names reserved.
"It will be an identifier quotation mark character (like the MySQL 'quotation mark character) rather than a string quotation mark character.
REAL will be a synonym for FLOAT, not a synonym for DOUBLE.
5.3 differences between MySQL and ANSI SQL92
We try to make MySQL comply with the ansi SQL and odbc SQL standards, but in some cases, MySQL does something different:
-- Only a comment, if followed by a white-space character. '--' Is the start of a comment.
For VARCHAR columns, when the value is stored, the space after the drag is deleted. See E known MySQL errors and design limitations.
In some cases, CHAR columns are secretly changed to VARCHAR columns. A quiet column specifies a change.
When you delete a table, the permissions on the table are not automatically abolished. You must explicitly issue a REVOKE to REVOKE permissions on a table. See section 7.26 GRANT and REVOKE syntax.

Functions lacking in MySQL

The following functions are not available in the current MySQL version. For a priority table indicating when new extensions can be added to MySQL, you should consult the online MySQL TODO table. This is the latest TODO table version in this manual. See F. The list of things we want to add to MySQL in the future (TODO ).
Sub-selection
The following statements in MySQL do not work yet:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 );
SELECT * FROM table1 WHERE id not in (SELECT id FROM table2 );
However, in many cases, you can rewrite the query without the sub-choice:
SELECT table1. * FROM table1, table2 WHERE table1.id = table2.id;
SELECT table1. * FROM table1 left join table2 ON table1.id = table2.id where table2.id IS NULL
For more complex subqueries, you can create temporary tables to save subqueries. However, in some cases, this option will not work. The most common case is the DELETE statement, which does not support join for standard SQL statements (except for sub-selection ). In this case, there are two available options until the sub-selection is supported by MySQL.
The first option is to use a procedural programming language (such as Perl or PHP) to submit a SELECT query to obtain the primary key of the records to be deleted, and then use these values to construct the DELETE Statement (delete from... WHERE... IN (key1, key2 ,...)).
The second option is to use interactive SQL to automatically construct a set of DELETE statements, and use MySQL extension CONCAT () to replace standard | Operator ). For example:
Select concat ('delete FROM tab1 WHERE pkid = ', tab1.pkid ,';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
You can place the query in a script file and redirect the input from it to the mysql command line interpreter, and return the output as a pipeline to the 2nd instances of the interpreter:
Prompt> mysql -- skip-column-names mydb <myscript. SQL | mysql mydb
MySQL only supports INSERT... SELECT... and REPLACE... SELECT ..., the independent sub-selection may be obtained IN 3.24.0. However, IN other environments, you can now use the function IN ().
SELECT INTO TABLE
MySQL does not support Oracle SQL expansion: SELECT... into table ...., on the contrary, MySQL supports ansi SQL syntax INSERT... SELECT ..., they are basically the same.
In addition, you can use select into outfile... or create table... SELECT to solve your problem.