Nonstandard or Unique SQL Constructs in MySQL (Extensions to ANSI SQL92)

only for RuBoard - do not distribute or recompile

Nonstandard or Unique SQL Constructs in MySQL (Extensions to ANSI SQL92)

The following list was taken from the MySQL Web site; you might want to check there for updated information (www.mysql.com). I have removed some items that you are not likely to ever use, and I have expanded certain items for clarity.

  • The field attributes AUTO_INCREMENT, BINARY, NULL, UNSIGNED and ZEROFILL. [1]

    [1] . 5 How standards-compatible is MySQL? / 5.1 MySQL extensions to ANSI SQL92. MySQL Reference Manual for Version 3.23.25-beta [online]. August 2000 [cited 1 September 2000]. Available from Internet: http://www.mysql.com/documentation/mysql/bychapter/manual_Compatibility.html

    • The AUTO_INCREMENT attribute may be applied to any integer column. When you attempt to insert a 0 or null, it will be set to the next highest value for that column by +1. If you delete the row with the current maximum value, it will not be reused. There can be only one AUTO_INCREMENT column per table, and it must be indexed. MySQL will work correctly only if the AUTO_INCREMENT column has positive values; inserting a negative number causes it to wrap and is considered a large positive value.

  • All string comparisons are case insensitive by default, with sort ordering determined by the current character set [1]

  • MySQL maps each database to a directory under the MySQL data directory, and tables within a database to filenames in the database directory. This has a few implications:

    • Database names and table names are case sensitive on operating systems that have case sensitive filenames (like most [Linux] systems).

    • Database, table, index, column or alias names may begin with a digit (but may not consist solely of digits).

    • You can use standard system commands to back up, rename, move, delete and copy tables. For example, to rename a table, rename the ˜.MYD , ˜.MYI and ˜.frm files to which the table corresponds. [1] (You should, of course, ensure that the MySQL server is not up when doing this.)

  • In SQL statements, you can access tables from different databases with the db_name.tbl_name syntax. [1]

  • LIKE is allowed on numeric columns . [1] For example, 10 LIKE '1%' is acceptable.

  • Use of INTO OUTFILE and STRAIGHT_JOIN in a SELECT statement. [1]

    • The INTO OUTFILE statement is the complement to the LOAD DATA INFILE statement. Usage is as follows :

       Select * from tbl_invoices INTO OUTFILE 'invoices.txt' 

      The file is created on the server, it cannot already exist, and the creator must have the file privilege on the server. By default, it creates a tab-delimited file with no headers. Also note that if the filename is given without a path , it is put in the database directory, not the directory you were in when you called mysql.

    • The STRAIGHT_JOIN statement forces the optimizer to join the tables in the order in which they appear in the FROM clause. This might improve performance if MySQL is joining the tables in a less-than -optimum way.

  • EXPLAIN SELECT to get a description on how tables are joined. [1] This is really a prepending of the EXPLAIN keyword to a select statement. MySQL returns information about how it would process the query, including how tables are joined and in what order. It does not return the rows described by the select statement, only the query processing information.

    • You can also use EXPLAIN table_name , which is the same as DESCRIBE table_name and SHOW COLUMNS from table_name.

  • Use of index names, indexes on a prefix of a field, and use of INDEX or KEY in a CREATE TABLE statement. [1]

  • Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE . [1]

    • If you create a table using the TEMPORARY keyword, that table will automatically be deleted if the connection is terminated . Two different connections can use the same table name in this case without conflict.

    • The IF NOT EXISTS keywords allow you to check for the existence of a table during the create statement and avoid an error if the table already exists. Note that it does not check the table s structure, only the name. Usage is as follows:

       CREATE [TEMPORARY] TABLE [IF NOT EXISTS] .... 
  • Use of COUNT (DISTINCT list) where ˜list is more than one element. [1] Usage is as follows:

     select count(distinct exp1, exp2, ...) from table_name. 

    This returns a count of distinct rows for the expressions listed ”in this case, distinct rows for exp1 + exp2.

  • Use of CHANGE col_name , DROP col_name or DROPINDEX in an ALTER TABLE statement. [1] For example:

     ALTER TABLE tbl_main CHANGE COLUMN age, age_prev INTEGER  ALTER TABLE tbl_main DROP COLUMN age  ALTER TABLE tbl_main DROP INDEX idx_age 

    Actually, in the first two examples, the COLUMN keyword is optional.

  • Use of multiple ADD , ALTER , DROP or CHANGE clauses in an ALTER TABLE statement. [1]

  • You can drop multiple tables with a single DROPTABLE statement. [1] Separate table names with commas.

  • The LIMIT clause of the DELETE statement. [1] Add a clause to the end of the DELETE statement with the number of rows to be deleted:

     DELETE from tbl_main where age > 55 LIMIT 10 
  • The DELAYED clause of the INSERT and REPLACE statements. [1] This means the client will get an immediate response from the server indicating success, but the server will insert the row when the table is not in use by any other thread. This primarily improves response time. Additionally, inserts from many clients are written together for improved speed. However, the inserted data is stored in memory, so if mysqld dies unexpectedly, any queued rows are lost.

  • Use of LOAD DATA INFILE . [1] You can read either from the client or server machine, but you must use the LOCAL keyword if you re reading from the client. Loading from the client is slower, but loading from the server requires the file permission. Use of the REPLACE and IGNORE keywords causes insert rows that violate KEY constraints to either replace existing rows or be discarded, respectively. The defaults expect a tab-delimited file with rows separated by newline characters and no fields enclosed by quotes, but these defaults can be overridden. There is also an IGNORE number LINES clause that can be set to ignore a number of lines ”column headers, for example. MySQL can read fixed-width files as long as the display width for the columns exactly matches the file to be read.

  • The OPTIMIZE TABLE statement. [1] This allows you to reclaim unused space after you have deleted a sizeable part of a table or if you have made changes to a table with variable-length columns (varchar, blob, or text). MySQL maintains deleted records as a linked list, and subsequent INSERT statements reuse old positions . The OPTIMIZE TABLE statement reclaims that space. While OPTIMIZE TABLE is executing, no access is allowed (which improves speed).

  • The SHOW statement. [1] SHOW displays information about databases, tables, or columns. It can also show information on indexes, status, variables , processes, and grants. In many cases, it can also use a LIKE clause with wildcards. For example:

     SHOW DATABASES  SHOW TABLES LIKE 'invoic%'; 

    The following are equivalent:

     SHOW INDEX FROM tbl_main FROM db_main;  SHOW INDEX FROM db_main.tbl_main; 
  • Strings may be enclosed by either " [double quotes] or ' [single quotes] [1]

  • The SET OPTION statement. [1] This allows you to set various options of your session with the server.

  • You don t need to name all selected columns in the GROUP BY part. This gives better performance for some very specific, but quite normal queries. [1] For example:

     select cust_num, cust_id, max(order_amt)  from tbl_invoices  GROUP BY cust_num 

    In ANSI SQL, you would have to add cust_name to the GROUP BY clause. Be advised that this requires you not only to know your data but also to be sure of your data integrity. Don t use this feature if the columns you omit aren t unique in the group! In my tests where the data was not unique, it returned the value in the first row it came to, but I have been able to find nothing that states this is the default behavior.

  • [A]ll string functions support both ANSI SQL syntax and ODBC syntax. [1]

  • MySQL understands the and && operators to mean logical OR and AND, as in the C programming language. In MySQL, and OR are synonyms, as are && and AND . Because of this nice syntax, MySQL doesn t support the ANSI SQL operator for string concatenation; use CONCAT() instead. Because CONCAT() takes any number of arguments, it s easy to convert use of the operator to MySQL. [1]

  • The CREATE DATABASE or DROP DATABASE [1] statements.

  • The = , <>, <= , < , >=, > , <<, >>, <=>, AND, OR or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements. For example:

     mysql> SELECT col1=1 AND col2=2 FROM tbl_name;"  [1]  

    This can be used as a true/false test, which makes the output columns of zeros or ones. In this example, the output would be two columns with the expressions as headers and zeros or ones for values ( assuming the columns are named col1 and col2 and the types are integer, with values that include 1 or 2).

  • The LAST_INSERT_ID() function. [1] This returns the last automatically generated value that was inserted into an AUTO_INCREMENT column.

  • CONCAT() or CHAR() with one argument or more than two arguments. (In MySQL, these functions can take any number of arguments.) [1]

  • Use of TRIM() to trim substrings. ANSI SQL only supports removal of single characters. [1]

  • Use of REPLACE instead of DELETE + INSERT. [1] REPLACE syntax is very similar to INSERT , except that if an old record in the table has the same value for a unique key, that record is deleted before the new record is inserted.

  • The FLUSH flush_option statement [1] where the most common flush options are HOSTS, LOGS, PRIVILEGES, or TABLES. HOSTS causes the host cache tables to be emptied; this can help if you get a Host is blocked message, which means that the IP address exceeded the max_connect_errors value and thus has been blocked from connecting again. LOGS closes and reopens the standard and update log files. PRIVILEGES reloads the privileges from the grant tables. TABLES closes all open tables.

Running MySQL in ANSI Mode

You can start mysqld with the --ansi option. When you do, MySQL handles certain things differently. This might be of importance to you primarily if you have portability issues with code or if you have unusual connections to other databases. [2]

[2] . 5 How standards-compatible is MySQL? / 5.2 Running MySQL in ANSI mode. MySQL Reference Manual for Version 3.23.25-beta [online]. August 2000 [cited 1 September 2000]. Available from Internet: http://www.mysql.com/documentation/mysql/bychapter/manual_Compatibility.html

Among the changes in the way mysql behaves: is used for string concatenation, REAL will mean FLOAT (not DOUBLE ), and so on. There are other behaviors as well; check http://www.mysql.com for the latest. [2]

only for RuBoard - do not distribute or recompile


MySQL Building User Interfaces
MySQL: Building User Interfaces (Landmark)
ISBN: 073571049X
EAN: 2147483647
Year: 2001
Pages: 119

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net