Mysql Chinese Reference Manual 6 (Mysql and Standard Compatibility) Reserved Translator: 子 〖〖〗 〖转
Q Translator: 子 (clyan@sohu.com) Home: http://linuxdb.yah.net5 mysql and standard compatibility? 5.1 MySQL The ANSI SQL92 expansion MySQL contains some expansion that may not be found in other SQL databases. Be careful if you use them, your code will not be compatible with other SQL servers. In some cases, you can write code including mysql extension, but still portable, by using / *! ... * / form. In this case, MySQL will perform lexical analysis and execute the code in the comment, 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 add a version number after '!', This syntax will only be applied or more in the mysql version. Create / *! 32302 Temporary * / table (a int); above means if you have 3.23.02 or update, MySQL will use the Temporary keyword. 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 strings are negligible, and the current character set is determined (default is ISO-8859-1Latin1) sort order. If you don't like this, you should use a binary property or use a Binary Qualification Declaration, which causes sorting according to the ASCII order of the MySQL server host. MySQL mapping each database below a mysql data directory, maps the database table to the database file name under the database directory. This has 2 points:
The database name and table name in MySQL on the operating system (like most UNIX systems) is case sensitive. The database name and table name are case sensitive. If you have difficulty remember the table name, accept a consistent agreement, such as creating a database and table with a lowercase name. Database, table, index, column or alias can begin with numbers (but cannot consist only by numbers). You can use a standard system command backup, rename, move, delete, and copy tables. For example, rename a table, rename ".myd", ".myi", and ".frm" files for the corresponding table in the SQL statement, you can access tables in different databases with db_name.tbl_name syntax. Some SQL servers provide the same feature but they call them User Space (user space). MySQL does not support table space similar to in Create Table Ralph.my_Table ... in my_tablespace. Like is allowed on the numeric column. Use Into Outfile and Straight_Join in a SELECT statement. See 7.12 SELECT Syntax. In a SELECT statement, SQL_SMALL_RESULT options. Explain Select gives a description of how to join a table. In a CREATE TABLE statement, use indexes 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), here "List" exceeds an 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 in the statement. Use multiple adds, alter, drop, or change clauses in an ALTER TABLE statement. Use Drop Table with keyword if exists. You can abandon multiple tables with a single Drop Table statement. DELETE statement LIMIT clause. DELAYED clause of INSERT and REPLACE statement. 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 7.16 Load Data Infile syntax. Optimize Table statement. See 7.9 Optimize Table syntax. SHOW statement. See 7.21 Show syntax (information on the table, column, etc.). The string can be surrounded by "" "", not just "'". Use "" escape character. SET OPTION statement. See 7.25 Set Option syntax. You don't need to be named all selected in the group By section Column. This gives better performance for some very specific cases, not a general query. See 7.4.13 For the function of the Group By clause. For convenience from the SQL environment for users, Mysql supports many functions Alias. For example, all string functions support ANSI SQL syntax and ODBC syntax. MySQL Comprehension || and && mean logical or and and and and, just like in the C program language. In MySQL, || and OR are synonymous && and and and and: It is because of this good syntax, mysql is not supported by the string; instead use concat (), because concat () accepts any number of parameters, it is easy to put || The operator uses transform to MySQL. CREATE DATABASE or DROP DATABASE.
See 7.5 CREATE DATABASE syntax. The% operator is mod () a synonym, ie, N% M equivalent to mod (n, m). % Supports C programmers and compatible with PostgreSQL. =, <>, <=, <,> =,>, <<, >>, <=>, AND, or LIKE operator can be used in the left side of the SELECT statement. 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 operator Regexp and Not Regexp. Concat () or char () has a parameter or more than 2 parameters. (In MySQL, these functions can take any quantity parameters.) Bit_count (), Case, Elt (), from_DAYS (), Format (), IF (), Password (), Encrypt (), MD5 (), Encode ), Decode (), period_add (), period_diff (), to_days (), or weekday () function. Use a TRIM () renovation. 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 syntact. FLUSH FLUSH_OPTION statement. In one statement: = Possibility of setting variables: select @a: = sum (total), @ b = count (*), @ a / @ b as avg from test_table; select @ T1: = (@ T2: = 1) @ T3: = 4, @ T1, @ T2, @ T3; 5.2 Run mysql in ansi mode If you start MySQLD, Mysql's following behavior changes. || Is the string and is not OR. Can be between a function name and "(" there is any number of spaces. This also makes all the function names a retention word. "Will be a marker quoter character (like the mysql` quotation marks) instead of a string quotation mark Character. REAL will be a synonym of Float, not a Double synonym. 5.3 MySQL compared to the difference between ANSI SQL92 We try to make MySQL compliance with the ANSI SQL standard and ODBC SQL standards, but in some cases, Mysql does something different:
- just a comment, if it is followed by a white empty character. See 5.4.7 `- 'begins as a comment. For a varchar column, when the value is stored, the slope is deleted. See E MySQL known mistakes and design lacks. In some cases, the Char column is vapridden to vARCHAR columns. See 7.7.1 Calm Columns Specify changes. When you delete a table, the permissions of the table are not automatically abolished. You must explicitly send a revoke to abolish the permissions of a table. See 7.26 GRANT and REVOKE syntax. 5.4 Mysql Lack Functions The following features are not available in the current MySQL version. For a priority table, when new extensions 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 join the Mysql list (TODO) in the future. 5.4.1 Sub Choose In Mysql, the following statement does not work: select * from table1 where id in (select); select * from table1 where id not in (select id from table2); however, in many cases, You can rewrite the query, instead of child selection: 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 sub-queries, you can usually create a temporary table preservation subquery. However, in some cases, this selection will not pass. The most frequently encountered is the delete statement, which is not supported for its standard SQL (Join). For this situation, there are 2 available options until the child selection is supported by MySQL. The first choice is to use a process-friendly program language (such as Perl or PHP) to submit a SELECT query to get the record primary key to be deleted, and then use these values to construct the DELETE statement (delete from ... where ... in (Key1, Key2, ...)). The second choice is to automatically construct a set of DELETE statements using the interactive SQL, use the MySQL extended concat () (instead of the 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 on a script file and redirect from it Enter the mysql command line interpreter, the output is used as the second example of the interpreter: Prompt> mysql --skip-color_ names mydb 5.4.3 Transaction does not support transaction processing. MySQL will support atomic operations in a short period of time, which is like a transaction that has not been returned. With atomic operations, you can execute a set of INSERT / SELECT / WHATER commands and guarantee that there is no other thread intervention. In this article, you usually do not need to return to the volume. Currently, you can block interference from other threads by using the Lock Tables and UNLOCK TABLES commands. See 7.24 Lock Tables / UNLOCK TABLES syntax. 5.4.4 Store 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 need to re-issue all queries, and can refer to the stored procedure. Because queries require only one lexical analysis and less information needs to be transmitted between servers and customers, this provides better performance. You can upgrade the concepts that you can improve by having a library in the server. A trigger is a stored procedure called when a special event occurs. For example, you can install a stored procedure, which triggers each time you delete a record from a trading table, and automatically remove the corresponding customer from a client table when all transactions are deleted. The scheduled modified language will process stored procedures, but there is no trigger. Triggers usually make everything slower, even for the query they don't need. For the two minutes, Mysql may get a stored procedure, see f We want to join the Mysql list (TODO) in the future. 5.4.5 Outside the foreign bond, in the SQL, foreign bonds are not used in the joint table, which is mainly used to check the reference integrity (RI). If you want to get the result from multiple tables with a SELECT statement, you do it through the joint table! Select * from table1, table2 where table1.id = Table2.id; see 7.13 JOIN syntax. See 8.3.5 Use the foreign key. There is a Foreign Key syntax existing in MySQL to be compatible with other SQL vendors; it does not do anything. The Foreign Key syntactics without On delete is mainly used for document purposes. Some ODBC applications can use it to automatically generate WHERE clauses, but this is usually easy to overwrite. The Foreign Key is sometimes used as a constraint check, but if the line is inserted in the correct order, the check is actually unnecessary. MySQL only supports these clauses (do not consider whether they work!), Because some applications require them to exist. In MySQL, you can solve the on Delete ... is not implemented, when you delete your record from a table with foreign key, you can add an appropriate DELETE statement for an application. In fact, this is very fast (faster in some cases) and more than easy porting than the foreign key. In the near future we will expand the Foreign Key implementation so that at least information will be saved in the table description file and can be retrieved by mysqldump and ODBC. 5.4.5.1 Reasons for non-use foreign keys There are a lot of questions related to Foreign Key. We don't know where to start: The foreign key makes life more complex, because the definition of foreign bonds must be stored in a database and implement all "Good Method" that will destroy the file that can be moved, copied, and delete files. Speed effects are terrible on the INSERT and UPDATE statements, and almost all Foreign Key checks in this case are useless, because any, in the correct table in the correct table, in the correct order. When updating a table, there is also a need to save the lock on many tables because the side effects can be connected in series through all databases. First remove the record from a table and then remove them from other tables, which is faster. You can no longer be able to remove a table (from a new source or from a backup) by doing a comprehensive table to delete and then restore all records. If you have a foreign key, you can't dump and recover the table unless you do this with a very specific. It is easy to make a "allowed" loop definition makes it impossible to reconstruct each table with a single CREATE statement, even if the definition is feasible. The only good aspect of Foreign Key is that it checks the ability to connect a table to ODBC and some other client programs and use them to display the connection chart and help construct applications. MySQL will soon store Foreign Key to define a customer to ask and receive an answer to the original connection. Current "FRM file format does not have its status. 5.4.6 View MySQL does not support view, but it is on Todo. 5.4.7 '-' As a note, some other SQL database use '-' Start comments. Mysql has "#" as a character that starts a number of comments, even if the mysql command line tool deletes all lines starting with '-'. You can also use C annotation style / * this is a comment * /. See 7.29 Comment syntax. MySQL3.23.3 and above support '-' comment style, as long as the comment is followed by a space. This is because this degraded annotation style has caused automatically generated SQL queries like the following code. Many problems, here we are automatically! Payment! Insert payment value: Update TBL_NAME SET CREDIT = CREDIT-! Payment! Do you think what will happen when payment is negative? Because 1--1 is in SQL is Legal, we think '-' Start comments are terrible. However, in MySQL 3.23, you can use: 1 - this is a comment If you are running a version of MySQL than 3.23, the following discussion is involved You: If you have a SQL program in a text file, it contains '-' comments, you should use: Shell> Replace "-" "#" 5.6 How to deal with a commit-rollback does not support Commit / RollbackMysql. The problem is to effectively handle commit-rollback will need a table layout that is completely different from MySQL today. MySQL will also require additional threads to automatically clear work on the table, and disk dosage will be higher. This will make MySQL more than about 2-4 times. MySQL is faster than almost all other SQL databases (generally at least 2-3 times). One of the reasons is the lack of commit-rollback. Currently, we are more implementing SQL server language (like stored procedures), with it, you will really need Commit-Rollback, which will also get better performance. A loop that usually requires transaction can be encoded with Lock Tables, and when you can instantly update records, you don't need a cursor (CURSOR). We have transactions and cursors on Todo, but it is not quite preferred. If we implemented these, as the Create Table option, it means that Commit-Rollback will only work on those tables so that the speed loss is only imposed on those tables. We have a bigger demand in TCX, a real fast database than 100% universal database. Whenever we find a way to achieve these features without any speed loss, we will might be it. Temporarily, there are many more important things to do. Check TODO to see how we will prioritize things at this time. (Some higher-level support customers can change it, so things can be re-prioritized.) The current problem is actually Rollback, without rollback, you can do any COMMIT actions with Lock Tables. In order to support Rollback, MySQL will have to be changed to store all old records, if rollback, they are updated and restore anything to the starting point. For simple situations, this is not difficult (current isamlog can be used here), but Rollback will be more difficult for ALLBACK for ALTER / DROP / CREATE TABLE. Avoid using Rollback, you can use the following strategies: Use Lock Tables ... locking all database tables you want to access. Test Conditions. If everything is correct, update. Use unlock table to release your lock. This is usually more faster than the transaction that may take rollback, although not always like this. The only condition that this solution cannot be processed is when someone kills the thread in the update. In this case, all locks will be released, but some changes cannot be performed. You can also make the function update records in a single operation. You can get a very efficient application by using the following techniques: Relative to their current value modification fields only updates those that actually changed, for example, when we are updating some customer information, we only update those customers who changed The data is only tested that there is no change in data, or the data depends on the changed data, which is changed compared to the original row. The test for the changed data is done in the UPDATE statement with the WHERE clause. If the record is not updated, we give the customer a message: "Some of the data you have changed have been changed by other users", then we show the old line of the new line of row in a window, so the user can decide which version he should use. Customer record. This gives us something similar to "column lock", but it is actually even better because we only update some columns and use values relative to their current values.
