mysql| Standard | reference | reference Manual | chinese
MySQL Chinese reference manual 6 (MySQL compatibility with the standard)
Reprinted translator: Yan Zi
return 〗〖 Forwarding
Q Translator: Yan Zi (clyan@sohu.com) Homepage: http://linuxdb.yeah.net
5 MySQL compatibility with the standard?
5.1 MySQL extended to ANSI SQL92
MySQL contains extensions that may not be found in other SQL databases. Be aware 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 extensions, but it's still portable, by using the/*! . * * form the annotation. In this case, MySQL will perform lexical analysis and execute the code within the annotation as if it were any other MySQL statement, but the other SQL Server will ignore the extension. For example:
SELECT/*! Straight_join * * col_name from Table1,table2 WHERE ...
If you're in '! ' After adding a version number, the syntax will only be executed if the MySQL version is equal to or newer than the version number used:
CREATE/*!32302 Temporary */TABLE (a int);
The above means that if you have 3.23.02 or newer, then MySQL will use temporary keywords.
The MySQL extensions are listed below:
Field type Mediumint, SET, enum, and different blob and text types.
Field Properties 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-1latin1) sort order. If you don't like this, you should use the binary attribute or declare the column with the binary coercion, which causes the MySQL server host to be sorted according to the ASCII order.
MySQL maps each database to a directory below the MySQL data directory, mapping the database table to the database file name under the database directory. This has 2 implications:
Database names and table names are case-sensitive in MySQL on the case-sensitive file name of the operating system (like most Unix systems). If you have difficulty remembering the table name, accept a consistent convention, such as always creating the database and table in lowercase names.
A database, table, index, column, or alias can start with a number (but not only by numbers).
You can use standard system commands to back up, rename, move, delete, and copy tables. For example, rename a table and rename it. MyD ",". Myi "and". frm "files as appropriate tables
In SQL statements, you can use Db_name.tbl_name syntax to access tables in different databases. Some SQL servers provide the same functionality but call them the user space. MySQL does not support similar in CREATE TABLE ralph.my_table ... The tablespace in the My_tablespace.
Like is allowed on numeric columns.
Use into outfile and straight_join in a SELECT statement. See 7.12 Select Syntax.
Sql_small_result the option in a SELECT statement.
EXPLAIN Select Gets a description of how the table is joined.
Use the index in a CREATE TABLE statement, the index on the field prefix, and use index or key. See 7.7 CREATE TABLE syntax.
CREATE table uses temporary or if not EXISTS.
Use COUNT (DISTINCT list), where "list" is more than one element.
Use the Change col_name, drop col_name, or drop INDEX inside an ALTER TABLE statement. See 7.8 ALTER table syntax.
In an ALTER TABLE, the statement uses ignore.
Use multiple add, alter, DROP, or change clauses in an ALTER table statement.
Use a 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 the INSERT, REPLACE, delete, and UPDATE statements.
Use the load DATA INFILE. In most cases, this syntax is compatible with Oracle's load DATA infile. See 7.16 LOAD DATA INFILE syntax.
OPTIMIZE Table statement. See 7.9 OPTIMIZE table syntax.
Show statement. See 7.21 Show syntax (get information about tables, columns, etc.).
Strings can be surrounded by "" or "", not just "'".
Use the escape character.
SET option statement. See 7.25 SET option syntax.
You do not need to name all the selected columns in the Group by section. This gives better performance for some very specific situations, rather than a generic query. See 7.4.13 functions for the GROUP BY clause.
For the convenience of other users from the SQL environment, MySQL supports aliases for many functions. For example, all string functions support ANSI SQL syntax and ODBC syntax.
MySQL Understanding | | and && means logical OR and and, just like in the C program language. In MySQL, | | And or are synonyms && and and are synonyms. Because of this good syntax, MySQL does not support the string with ANSI SQL | | operator, instead of using concat (), because Concat () accepts any number of parameters, it is easy to put | | The operator uses the transform to MySQL.
CREATE database or drop database. See 7.5 CREATE database syntax.
The% operator is a synonym for mod (), i.e., N% M equivalent to mod (n,m). % supports C programmers and is compatible with PostgreSQL.
=, <>, <=,<, >=,>, <<, >>, <=>, and, or like operators can be placed on the from left of the SELECT statement to compare columns. For example:
Mysql> SELECT col1=1 and col2=2 from Tbl_name;
LAST_INSERT_ID () function. See 20.4.29 mysql_insert_id ().
Extended regular expression operators REGEXP and not REGEXP.
CONCAT () or char () has a parameter or more than 2 parameters. (in MySQL, these functions are acceptable in 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 refurbish the substring. ANSI SQL only supports deletion of individual characters.
GROUP by Function std (), Bit_or (), and Bit_and ().
Use replace instead of Delete+insert. See 7.15 Replace Syntax.
FLUSH flush_option statement.
The possibility of setting a variable in a statement with: =:
SELECT @a:=sum (total), @b=count (*), @a/@b as Avg. from test_table;
SELECT @t1: = (@t2: =1) + @t3: =4, @t1, @t2, @t3;
5.2 Running MySQL in ANSI mode
If you start mysqld,mysql with the--ansi option, the following behavior changes.
|| is a string and not an OR.
You can have any number of spaces between a function name and "("). This also makes all functional names a reserved word.
"will be an identifier quote character (like the MySQL ' quote character) instead of a string quote character.
Real will be a synonym for float, not a double synonym.
5.3 MySQL is different than ANSI SQL92
We tried to make MySQL conform to ANSI SQL standards and ODBC SQL standards, but in some cases MySQL does something different:
--Just a comment, if followed by a white-space character. See 5.4.7 '--' as the beginning of a note.
For varchar columns, the trailing spaces are deleted when the value is stored. See e MySQL known bugs and design limitations.
In some cases, the Char column is secretly changed to the varchar column. See 7.7.1 Calm column to specify the changes.
When you delete a table, the permissions on the table are not automatically revoked. You must explicitly issue a revoke to revoke permissions on a table. See 7.26 Grant and REVOKE syntax.
5.4 MySQL lack of functionality
The following features are not available in the current MySQL version. For a priority table to indicate when a new extension can be added to MySQL, you should consult the online MySQL TODO table. This is the latest TODO table version of this manual. See F We want to add a list of things to MySQL in the future (TODO).
5.4.1 Child Selection
The following statements are not working in MySQL:
SELECT * FROM table1 WHERE ID in (select ID from table2);
SELECT * FROM table1 WHERE ID is not in (select ID from table2);
However, in many cases, you can rewrite the query without having to select the child:
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 usually create a temporary table-saving subquery. In some cases, however, this option will not work. The most frequently encountered scenario is the DELETE statement, which does not support join (except in the child selection) for standard SQL. For this scenario, there are 2 available options until the child selection is supported by MySQL.
The first option is to submit a select query using a procedural language (such as Perl or PHP) to get the primary key for the deleted record, and then construct the DELETE statement using these values (delete from ...). WHERE ... In (Key1, Key2, ...)).
The second option is to use interactive SQL to automatically construct a set of DELETE statements, using MySQL to extend concat () (instead of standard | | operator). For example:
SELECT CONCAT (' DELETE from tab1 WHERE pkid = ', Tab1.pkid, '; ')
From Tab1, TAB2
WHERE tab1.col1 = tab2.col2;
You can put this query in a script file and redirect it into the MySQL command line interpreter, and return its output as a pipe to the 2nd instance of the interpreter:
prompt> MySQL--skip-column-names mydb < MyScript.sql | MySQL MyDB
MySQL only supports inserts ... SELECT ... and replace ... Select ..., the independent sub selection will probably get in 3.24.0, however, in other circumstances, you can now use the function in ().
5.4.2 SELECT into TABLE
MySQL does not yet support Oracle SQL extensions: SELECT ... Into TABLE ..., instead MySQL supports ANSI SQL syntax insert INTO ... SELECT ..., basically they are the same.
In addition, you can use SELECT INTO OutFile ... or create TABLE ... Select to solve your problem.
5.4.3 Transaction Processing
Transaction processing is not supported. MySQL will support Atomic (atomic) operations in a short time, like a transaction that does not have a rollback. With atomic manipulation, you can perform a set of INSERT/SELECT/WHATEVER commands and ensure that no other threads are involved. In this article, you usually don't need to roll back. Currently, you can block other threads from interfering by using the lock tables and the Unlock tables command. See 7.24 LOCK tables/unlock TABLES syntax.
5.4.4 stored procedures and triggers
A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this is done, the customer does not have to issue all of the queries, but can refer to the stored procedure. This provides better performance because the query requires lexical analysis only once and less information needs to be transferred between the server and the customer. You can upgrade the conceptual hierarchy with the function libraries that you have in the server.
A trigger is a stored procedure that is invoked when a particular event occurs. For example, you can install a stored procedure that triggers every time a record is deleted from a transaction table, and automatically deletes the corresponding customer from a customer table when all its transactions are deleted.
The language you plan to modify will be able to handle stored procedures, but no triggers. Triggers usually make everything slower, even for queries they don't need.
For both solutions when MySQL may get stored procedures, see F We want to add a list of things to MySQL in the future (TODO).
5.4.5 FOREIGN Key
Note that the foreign key in SQL is not used for join tables, but is primarily used to check referential integrity (RI). If you want to get results from multiple tables with a SELECT statement, you do it by joining the table!
SELECT * from table1,table2 where table1.id = table2.id;
See 7.13 join syntax. See 8.3.5 using foreign keys.
The existence of foreign key syntax in MySQL is only compatible with other SQL vendor CREATE table commands; it doesn't do anything. No on DELETE ... The foreign key syntax is mainly used for document purposes. Some ODBC applications can use it to generate a WHERE clause automatically, but this is usually easily overwritten. The FOREIGN key is sometimes used as a constraint check, but if the row is inserted into the table in the correct order, the check is actually unnecessary. MySQL only supports these clauses (regardless of whether they work!) because some applications require that they exist.
In MySQL, you can resolve the on DELETE ... The problem that is not implemented is that when you delete a record from a table with a foreign key, you can add the appropriate DELETE statement to an application. In fact, this is fast (in some cases faster) and easier to migrate than using foreign keys.
In the near future we will expand the foreign key implementation so that at least the information will be saved in the table description file and can be retrieved by Mysqldump and ODBC.
5.4.5.1 reasons not to use foreign keys
There are a lot of problems with foreign key we don't know where to start:
Foreign keys make life more complex, because the definition of foreign keys must be stored in a database and implemented that they will destroy all "good methods" of using files that can be moved, copied, and deleted.
The speed impact is scary for INSERT and UPDATE statements, and in this case almost all foreign key checks are useless because you usually insert records in the correct order in the correct sequence.
When you update a table, there is also a need to save locks on many tables, because side effects can be concatenated through the entire database. It is quicker to delete records from a table and then remove them from other tables.
You can no longer recover a table (from a new source or from a backup) by doing a comprehensive table deletion and then recovering all of the records.
If you have a foreign key, you can't dump and recover the table unless you do it with a very specific one.
It's easy to make a "allowed" loop definition that makes it impossible to recreate each table with a single create statement, even if it is feasible and usable.
The only good thing about FOREIGN key is that it gives ODBC and some other client programs the ability to check how a table is connected, and use them to show the connection graph and help construct the application.
MySQL will soon be storing the foreign key definition so that a customer can ask and receive an answer to how the original connection was made. The current ". frm file format does not have the status it deserves.
5.4.6 View
MySQL does not support views, but it is on Todo.
5.4.7 '--' as the beginning of a note
Some other SQL databases use '--' to begin commenting. MySQL has "#" as the starting number annotation character, even if the MySQL command-line tool deletes all rows starting with '--'. You can also use the C annotation style/* This is a comment/* in MySQL. See 7.29 annotation syntax.
The MySQL3.23.3 and above versions support the '--' annotation style, as long as the annotation follows a space. This is because this degraded annotation style has caused many problems with automatically generated SQL queries like the following code, where we automatically insert payment values for!payment!:
UPDATE tbl_name SET credit=credit-!payment!
Do you think what will happen when the value of payment is negative?
Because 1--1 is legal in SQL, we think '--' starting comment is scary.
In MySQL 3.23, however, you can use: 1--this is a comment
If you are running a version of MySQL earlier than 3.23, the following discussion will involve you:
If you have a SQL program in a text file that contains '--' annotations, you should use:
shell> Replace "--" "#" < Text-file-with-funny-comments.sql
| MySQL Database
Rather than usually:
shell> MySQL Database < Text-file-with-funny-comments.sql
You can also "field" Edit command file to change '--' note to ' # ' NOTE:
5.5 What standards does MySQL follow?
Entry level SQL92. ODBC level 0-2.
5.6 How to deal with no commit/rollback
MySQL does not support commit-rollback. The problem is that effectively handling commit-rollback will require a completely different table layout than MySQL uses today. MySQL will also need extra threads to do the automatic cleanup on the table, and the disk usage will be higher. This will make MySQL about 2-4 times slower than it is today. MySQL is faster (generally at least 2-3 times faster) than almost any other SQL database. One reason is the lack of commit-rollback.
At present, we are more implementing SQL Server language (like stored procedures), with it, you will really rarely need commit-rollback, which will also get better performance.
A loop of transactions is usually required to encode with lock tables, and you do not need a cursor (cursor) When you can update records instantly.
We have transactions and cursors on Todo, but not quite a priority. If we implement these, this will be the option for CREATE table, which means that Commit-rollback will only work on those tables so that the speed loss is only imposed on those tables.
We have a much larger demand in tcx, a really fast database than the 100% universal database. Whenever we find a way to implement these features without any speed loss, we will probably do it. For the time being, there are many more important things to do. Check the Todo and see how we prioritize things at this point. (some customers with higher levels of support can change it, so things can be prioritized.) ）
The current problem is actually rollback, without rollback, you can do any commit with lock tables. To support Rollback,mysql will have to be changed to store all the old records, and if rollback are issued, they are updated and everything is restored to the starting point. This is not difficult for a simple situation (the current isamlog can be used for this purpose), but it will be more difficult to implement rollback for alter/drop/create table.
To avoid using rollback, you can use the following strategies:
Use lock TABLES ... Lock all the database tables you want to access.
Test conditions.
If everything is correct, update.
Use unlock tables to release your locks.
This is usually a quicker way than using a transaction that may take rollback, although not always. The only situation that this solution cannot handle is when someone kills a thread in an update. In this case, all the locks are freed, but some changes cannot be performed.
You can also make a function update a record with a single action. You can get an efficient application by using the following techniques:
Modify fields relative to their current values
Just update the fields that actually change
For example, when we are updating some customer information, we only update the changed customer data and test for no data changes, or the data depends on the changed data, as compared to the original row. The test for the changed data is completed in the UPDATE statement with a WHERE clause. If the record is not updated, we give the customer a message: "Some of the data you changed has been changed by another user," and then we display the new line in a window as usual, so the user can decide which version of the customer record he should use.
This gives us something like "column locking," but it's actually even better because we just update some columns and use values relative to their current values. This means that a typical UPDATE statement looks like these things:
UPDATE tablename SET pay_back=pay_back+ ' relative change ';
UPDATE Customer
SET
Customer_date= ' Current_date ',
address= ' new address ',
phone= ' new phone ',
money_he_owes_us=money_he_owes_us+ ' New_money '
WHERE
Customer_id=id and address= ' old address ' and phone= ' old phone ';
As you can see, this is very effective and can work even if other customers have changed the Pay_back or Money_he_owes_us columns.
In many cases, the user already wants to rollback or lock tables for the purpose of managing the unique identifiers of some tables. This can be handled more efficiently by a auto_increment column and a SQL function last_insert_id () or C API function mysql_insert_id (). See 20.4.29 mysql_insert_id ().
In TCX, we never have any requirement for row-level locking because we can always solve it by coding. There are cases where you need to do a real row lock, but they are rare. If you want row-level locking, you can use a flag column in a table and do this:
UPDATE tbl_name SET row_flag=1 WHERE id=id;
If the row is found and Row_flag is not 1 in the original row, MySQL returns 1 for the number of rows affected.
You can think of it because MySQL changes the query above to:
UPDATE tbl_name SET row_flag=1 WHERE id=id and Row_flag <> 1;
'
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
