... Complete!!! ,"VbDj3 $ss+//./Zk^L%mM rQQՙ>ca'\8NdFz4q׺c/LH}Jx &:~>j9nn$$rnvǢ:ʏ?4zl|T|C^5M -[ \PzSn>qs̎*]0QVa3oNl~nϒ_ycr M%j,vPH84Ķ+[<mwX#IB.5uyZ$nw0cD~Y,f&Vr­P64ۯ}N"vI}fQ)BmSzT28L c=(IUeeMF4kr,KE v$^5?lvKDR"z@~lDLjV-ɸSƝ)ژ ~/<ΝsWn?Rz;|#W])ޟ. endstreamendobj137 0 obj<>stream Adobed        s!1AQa"q2B#R3b$r%C4Scs5D'6Tdt& EFVU(eufv7GWgw8HXhx)9IYiy*:JZjzm!1AQa"q2#BRbr3$4CS%cs5DT &6E'dtU7()󄔤euFVfvGWgw8HXhx9IYiy*:JZjz?8.Wng o9,BIۓC(+DG'b95$k"%DuX>,?U؂M}qNh*Vs{{$&߰8 0ed~'h;Iw.ؤ)OO4SYkls7 aQ-r;۾"PhRXƌ.N8PWa#91LC;b'ŗbC"2J,}3fiMǾ0bD{ݍW Ïri5(+ʃ*..71=IK"XݲvBi/E=/X56rv\"v^ z Z\H/Ǩ,"$$[[xb aY6dQIޝђ n69jm^4П4y a-y7f"Z{|2oVsrpO D ҠLFLڵxjq`#%֖ic;XI) BTQ^2#d = QPZ}`jWsa%NqpZ)Ǧ]MIO #(BU5>1qRHIAv%a;2U߸һw%˨두Mleq4;I[r.kF&îB&?g$ E"Dr@dg$;,唢 W).˸Aq1D;(_>]=۰6D>"+|3$3'`TgBG/'U-M7E2j= "O3{SC CEӳ'>7V1āL*5s]9򝔌{μF[}@7І;>o5ѿy{'SrνKv#"zlfK?%iMKke#[Fiv\5>k3K_+Î87@_u4l=q:~[iVم(Ap$Onkj׽ʗ;.7 y:q,H//7@ӣӖm64v* :ԁ,U^Og4u^n]) U&?Hm>O%.̤Yɨ$ >`Xȹ_'K+7:0(3,70,!V5^IZ\{ڟ˂&q%;C:)VHQQ>xApLZ`_L)skrD2")ROJiB<zχH099r9p庭&;-uJR:;ɋJrL&ƟHúӲ~bO1%|v2v C˟ )PrqKË}@)v>I;xIT*E8'ߦH𧊃=zYڿi0dpVvc@@I9[G*VK,l,dsj@f o㍏|This statement drops the user-defined function (UDF) named function_name. To drop a function, you must have the DELETE privilege for the mysql database. This is because DROP FUNCTION removes a row from the mysql.func system table that records the function's name, type, and shared library name. drop-functionv2rSTDDEVSSyntax: STD(expr) STDDEV(expr) Returns the population standard deviation of expr. This is an extension to standard SQL. The STDDEV() form of this function is provided for compatibility with Oracle. As of MySQL 5.0.3, the standard SQL function STDDEV_POP() can be used instead. These functions return NULL if there were no matching rows. group-by-functions"s PERIOD_ADDSyntax: PERIOD_ADD(P,N) Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value. 4mysql> SELECT PERIOD_ADD(9801,2); -> 199803 date-and-time-functionsW"t|Syntax: | Bitwise OR: %mysql> SELECT 29 | 15; -> 31 bit-functions2u GEOMFROMTEXTGeomFromText(wkt[,srid]), GeometryFromText(wkt[,srid]) Constructs a geometry value of any type using its WKT representation and SRID. gis-wkt-functions"vRIGHT!RSyntax: RIGHT(str,len) Returns the rightmost len characters from the string str. 7mysql> SELECT RIGHT('foobarbar', 4); -> 'rbar' string-functions"wDATEDIFFSyntax: DATEDIFF(expr,expr2) DATEDIFF() returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30'); -> 1 mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31'); -> -31 date-and-time-functions$2xDROP PROCEDURE#Syntax: DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name This statement is used to drop a stored procedure or function. That is, the specified routine is removed from the server. As of MySQL 5.0.3, you must have the ALTER ROUTINE privilege for the routine. (That privilege is granted automatically to the routine creator.) The IF EXISTS clause is a MySQL extension. It prevents an error from occurring if the procedure or function does not exist. A warning is produced that can be viewed with SHOW WARNINGS. drop-procedure2y CHECK TABLESyntax: CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED} CHECK TABLE checks a table or tables for errors. CHECK TABLE works for MyISAM, InnoDB, and (as of MySQL 5.0.16) ARCHIVE tables. For MyISAM tables, the key statistics are updated as well. As of MySQL 5.0.2, CHECK TABLE can also check views for problems, such as tables that are referenced in the view definition that no longer exist. check-table"zBIN!Syntax: BIN(N) Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL. )mysql> SELECT BIN(12); -> '1100' string-functions"{CASE FUNCTIONSyntax: CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part. mysql> SELECT CASE 1 WHEN 1 THEN 'one' -> WHEN 2 THEN 'two' ELSE 'more' END; -> 'one' mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; -> 'true' mysql> SELECT CASE BINARY 'B' -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; -> NULL control-flow-functions2|DECLARE CURSOR#Syntax: DECLARE cursor_name CURSOR FOR select_statement This statement declares a cursor. Multiple cursors may be declared in a routine, but each cursor in a given block must have a unique name. The SELECT statement cannot have an INTO clause. declare-cursors2}MULTILINESTRINGMultiLineString(ls1,ls2,...) Constructs a WKB MultiLineString value using WKB LineString arguments. If any argument is not a WKB LineString, the return value is NULL. gis-mysql-specific-functionsc2~ LOAD DATAFSyntax: LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...)] The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The filename must be given as a literal string. LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. (See [select].) To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE. The syntax of the FIELDS and LINES clauses is the same for both statements. Both clauses are optional, but FIELDS must precede LINES if both are specified. For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, see [insert-speed]. The character set indicated by the character_set_database system variable is used to interpret the information in the file. SET NAMES and the setting of character_set_client do not affect interpretation of input. Note that it is currently not possible to load data files that use the ucs2 character set. As of MySQL 5.0.19, the character_set_filesystem system variable controls the interpretation of the filename. You can also load data files by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE statement to the server. The --local option causes mysqlimport to read data files from the client host. You can specify the --compress option to get better performance over slow networks if the client and server support the compressed protocol. See [mysqlimport]. If you use LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading from the table. If you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing. Using this option affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time. The LOCAL keyword, if specified, is interpreted with respect to the client end of the connection: o If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full pathname to specify its exact location. If given as a relative pathname, the name is interpreted relative to the directory in which the client program was started. o If LOCAL is not specified, the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file: o If the filename is an absolute pathname, the server uses it as given. o If the filename is a relative pathname with one or more leading components, the server searches for the file relative to the server's data directory. o If a filename with no leading components is given, the server looks for the file in the database directory of the default database. Note that, in the non-LOCAL case, these rules mean that a file named as ./myfile.txt is read from the server's data directory, whereas the file named as myfile.txt is read from the database directory of the default database. For example, if db1 is the default database, the following LOAD DATA statement reads the file data.txt from the database directory for db1, even though the statement explicitly loads the file into a table in the db2 database: LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table; Windows pathnames are specified using forward slashes rather than backslashes. If you do use backslashes, you must double them. For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See [privileges-provided]. load-data}2 LOCALTIMERSyntax: LOCALTIME, LOCALTIME() LOCALTIME and LOCALTIME() are synonyms for NOW(). date-and-time-functions2MPOINTFROMTEXTMPointFromText(wkt[,srid]), MultiPointFromText(wkt[,srid]) Constructs a MULTIPOINT value using its WKT representation and SRID. gis-wkt-functions2BLOBBLOB[(M)] A BLOB column with a maximum length of 65,535 (216 - 1) bytes. An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest BLOB type large enough to hold values M bytes long. string-type-overview"PASSWORD Syntax: PASSWORD(str) Calculates and returns a password string from the plaintext password str and returns a binary string, or NULL if the argument was NULL. This is the function that is used for encrypting MySQL passwords for storage in the Password column of the user grant table. Ymysql> SELECT PASSWORD('badpwd'); -> '*AAB3E285149C0135D51A520E1940DD3263DC008C' encryption-functionsI2CHAR&[NATIONAL] CHAR(M) A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length. The range of M is 0 to 255 characters. Note: Trailing spaces are removed when CHAR values are retrieved. Before MySQL 5.0.3, a CHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length. For example, CHAR(500) is converted to TEXT, and CHAR(200000) is converted to MEDIUMTEXT. This is a compatibility feature. However, this conversion causes the column to become a variable-length column, and also affects trailing-space removal. In MySQL 5.0.3 and later, if you attempt to set the length of a CHAR greater than 255, the CREATE TABLE or ALTER TABLE statement in which this is done fails with an error: mysql> CREATE TABLE c1 (col1 INT, col2 CHAR(500)); ERROR 1074 (42000): Column length too big for column 'col' (max = 255); use BLOB or TEXT instead mysql> SHOW CREATE TABLE c1; ERROR 1146 (42S02): Table 'test.c1' doesn't exist string-type-overview,"UTC_DATESyntax: UTC_DATE, UTC_DATE() Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context. Lmysql> SELECT UTC_DATE(), UTC_DATE() + 0; -> '2003-08-14', 20030814 date-and-time-functions" DIMENSION Dimension(g) Returns the inherent dimension of the geometry value g. The result can be -1, 0, 1, or 2. The meaning of these values is given in [gis-class-geometry]. =mysql> SELECT Dimension(GeomFromText('LineString(1 1,2 2)')); +------------------------------------------------+ | Dimension(GeomFromText('LineString(1 1,2 2)')) | +------------------------------------------------+ | 1 | +------------------------------------------------+ #general-geometry-property-functions"COUNT DISTINCTSyntax: COUNT(DISTINCT expr,[expr...]) Returns a count of the number of different non-NULL values. COUNT(DISTINCT) returns 0 if there were no matching rows. 4mysql> SELECT COUNT(DISTINCT results) FROM student; group-by-functions/2BIT BIT[(M)] A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted. This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, and BDB. Before 5.0.3, BIT is a synonym for TINYINT(1). numeric-type-overview2EQUALSOEquals(g1,g2) Returns 1 or 0 to indicate whether g1 is spatially equal to g2. SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); -> 3 mysql> SELECT INTERVAL(10, 1, 10, 100, 1000); -> 2 mysql> SELECT INTERVAL(22, 23, 30, 44, 200); -> 0 comparison-operators" FROM_DAYSBSyntax: FROM_DAYS(N) Given a day number N, returns a DATE value. 9mysql> SELECT FROM_DAYS(729669); -> '1997-10-07' date-and-time-functions2ALTER PROCEDURE#Syntax: ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...] characteristic: { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' This statement can be used to change the characteristics of a stored procedure or function. As of MySQL 5.0.3, you must have the ALTER ROUTINE privilege for the routine. (That privilege is granted automatically to the routine creator.) If binary logging is enabled, this statement might also require the SUPER privilege, as described in [stored-procedure-logging]. More than one change may be specified in an ALTER PROCEDURE or ALTER FUNCTION statement. alter-procedure" BIT_COUNTQSyntax: BIT_COUNT(N) Returns the number of bits that are set in the argument N. Cmysql> SELECT BIT_COUNT(29), BIT_COUNT(b'101010'); -> 4, 3 bit-functionsl"UTC_TIMESTAMPSyntax: UTC_TIMESTAMP, UTC_TIMESTAMP() Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. emysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0; -> '2003-08-14 18:08:04', 20030814180804 date-and-time-functionsW"+Syntax: + Addition: mysql> SELECT 3+5; -> 8 arithmetic-functions" INET_NTOASyntax: INET_NTOA(expr) Given a numeric network address (4 or 8 byte), returns the dotted-quad representation of the address as a string. Amysql> SELECT INET_NTOA(3520061480); -> '209.207.224.40' miscellaneous-functions,"ACOSSyntax: ACOS(X) Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1. mysql> SELECT ACOS(1); -> 0 mysql> SELECT ACOS(1.0001); -> NULL mysql> SELECT ACOS(0); -> 1.5707963267949 mathematical-functions2 ISOLATIONSyntax: SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } This statement sets the transaction isolation level for the next transaction, globally, or for the current session. The default behavior of SET TRANSACTION is to set the isolation level for the next (not yet started) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point on. Existing connections are unaffected. You need the SUPER privilege to do this. Using the SESSION keyword sets the default transaction level for all future transactions performed on the current connection. For descriptions of each InnoDB transaction isolation level, see [innodb-transaction-isolation]. InnoDB supports each of these levels in MySQL 5.0. The default level is REPEATABLE READ. To set the initial default global isolation level for mysqld, use the --transaction-isolation option. See [server-options]. set-transaction"CEILINGQSyntax: CEILING(X), CEIL(X) Returns the smallest integer value not less than X. Smysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEIL(-1.23); -> -1 mathematical-functions"SINDSyntax: SIN(X) Returns the sine of X, where X is given in radians. emysql> SELECT SIN(PI()); -> 1.2246063538224e-16 mysql> SELECT ROUND(SIN(PI())); -> 0 mathematical-functions" DAYOFWEEKSyntax: DAYOFWEEK(date) Returns the weekday index for date (1 = Sunday, 2 = Monday, ..., 7 = Saturday). These index values correspond to the ODBC standard. 4mysql> SELECT DAYOFWEEK('1998-02-03'); -> 3 date-and-time-functions2 LINEFROMWKB}LineFromWKB(wkb[,srid]), LineStringFromWKB(wkb[,srid]) Constructs a LINESTRING value using its WKB representation and SRID. gis-wkb-functions2SHOW PROCESSLISTSyntax: SHOW [FULL] PROCESSLIST SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). See [kill]. If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field. show-processlist " GEOMETRYTYPE GeometryType(g) Returns as a string the name of the geometry type of which the geometry instance g is a member. The name corresponds to one of the instantiable Geometry subclasses. mysql> SELECT GeometryType(GeomFromText('POINT(1 1)')); +------------------------------------------+ | GeometryType(GeomFromText('POINT(1 1)')) | +------------------------------------------+ | POINT | +------------------------------------------+ #general-geometry-property-functions<2 CREATE VIEW$ CREATE VIEW test.v AS SELECT * FROM t; Base tables and views share the same namespace within a database, so a database cannot contain a base table and a view that have the same name. Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, the optional column_list clause can be given as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement. Columns retrieved by the SELECT statement can be simple references to table columns. They can also be expressions that use functions, constant values, operators, and so forth. Unqualified table or view names in the SELECT statement are interpreted with respect to the default database. A view can refer to tables or views in other databases by qualifying the table or view name with the proper database name. A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries. The SELECT need not even refer to any tables. The following example defines a view that selects two columns from another table, as well as an expression calculated from those columns: mysql> CREATE TABLE t (qty INT, price INT); mysql> INSERT INTO t VALUES(3, 50); mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t; mysql> SELECT * FROM v; +------+-------+-------+ | qty | price | value | +------+-------+-------+ | 3 | 50 | 150 | +------+-------+-------+ A view definition is subject to the following restrictions: o The SELECT statement cannot contain a subquery in the FROM clause. o The SELECT statement cannot refer to system or user variables. o The SELECT statement cannot refer to prepared statement parameters. o Within a stored routine, the definition cannot refer to routine parameters or local variables. o Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. To check a view definition for problems of this kind, use the CHECK TABLE statement. o The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view. o The tables named in the view definition must already exist. o You cannot associate a trigger with a view. ORDER BY is allowed in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY. For other options or clauses in the definition, they are added to the options or clauses of the statement that references the view, but the effect is undefined. For example, if a view definition includes a LIMIT clause, and you select from the view using a statement that has its own LIMIT clause, it is undefined which limit applies. This same principle applies to options such as ALL, DISTINCT, or SQL_SMALL_RESULT that follow the SELECT keyword, and to clauses such as INTO, FOR UPDATE, LOCK IN SHARE MODE, and PROCEDURE. If you create a view and then change the query processing environment by changing system variables, that may affect the results that you get from the view: mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65)); Query OK, 0 rows affected (0.00 sec) mysql> SET NAMES 'latin1'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM v; +-------------------+---------------------+ | CHARSET(CHAR(65)) | COLLATION(CHAR(65)) | +-------------------+---------------------+ | latin1 | latin1_swedish_ci | +-------------------+---------------------+ 1 row in set (0.00 sec) mysql> SET NAMES 'utf8'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM v; +-------------------+---------------------+ | CHARSET(CHAR(65)) | COLLATION(CHAR(65)) | +-------------------+---------------------+ | utf8 | utf8_general_ci | +-------------------+---------------------+ 1 row in set (0.00 sec) The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time. They were addded in MySQL 5.0.13, but have no effect until MySQL 5.0.16. CURRENT_USER also can be given as CURRENT_USER(). Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine creator. This also affects a view defined within such a routine, if the view definition contains a DEFINER value of CURRENT_USER. The default DEFINER value is the user who executes the CREATE VIEW statement. (This is the same as DEFINER = CURRENT_USER.) If a user value is given, it should be a MySQL account in 'user_name'@'host_name' format (the same format used in the GRANT statement). The user_name and host_name values both are required. If you specify the DEFINER clause, you cannot set the value to any user but your own unless you have the SUPER privilege. These rules determine the legal DEFINER user values: o If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account. o If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated. The SQL SECURITY characteristic determines which MySQL account to use when checking access privileges for the view when the view is executed. The legal characteristic values are DEFINER and INVOKER. These indicate that the view must be executable by the user who defined it or invoked it, respectively. The default SQL SECURITY value is DEFINER. As of MySQL 5.0.16 (when the DEFINER and SQL SECURITY clauses were implemented), view privileges are checked like this: o At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to a stored function, only the privileges needed to invoke the function can be checked. The privileges required when the function runs can be checked only as it executes: For different invocations of the function, different execution paths within the function might be taken. o At view execution time, privileges for objects accessed by the view are checked against the privileges held by the view creator or invoker, depending on whether the SQL SECURITY characteristic is DEFINER or INVOKER, respectively. o If view execution causes execution of a stored function, privilege checking for statements executed within the function depend on whether the function is defined with a SQL SECURITY characteristic of DEFINER or INVOKER. If the security characteristic is DEFINER, the function runs with the privileges of its creator. If the characteristic is INVOKER, the function runs with the privileges determined by the view's SQL SECURITY characteristic. Prior to MySQL 5.0.16 (before the DEFINER and SQL SECURITY clauses were implemented), privileges required for objects used in a view are checked at view creation time. Example: A view might depend on a stored function, and that function might invoke other stored routines. For example, the following view invokes a stored function f(): CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name); Suppose that f() contains a statement such as this: IF name IS NULL then CALL p1(); ELSE CALL p2(); END IF; The privileges required for executing statements within f() need to be checked when f() executes. This might mean that privileges are needed for p1() or p2(), depending on the execution path within f(). Those privileges need to be checked at runtime, and the user who must possess the privileges is determined by the SQL SECURITY values of the function f() and the view v. The DEFINER and SQL SECURITY clauses for views are extensions to standard SQL. In standard SQL, views are handled using the rules for SQL SECURITY INVOKER. If you invoke a view that was created before MySQL 5.0.13, it is treated as though it was created with a SQL SECURITY DEFINER clause and with a DEFINER value that is the same as your account. However, because the actual definer is unknown, MySQL issues a warning. To make the warning go away, it is sufficient to re-create the view so that the view definition includes a DEFINER clause. The optional ALGORITHM clause is a MySQL extension to standard SQL. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED if no ALGORITHM clause is present. The algorithm affects how MySQL processes the view. For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement. For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement. For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used. A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long. A view algorithm can be UNDEFINED for three reasons: o No ALGORITHM clause is present in the CREATE VIEW statement. o The CREATE VIEW statement has an explicit ALGORITHM = UNDEFINED clause. o ALGORITHM = MERGE is specified for a view that can be processed only with a temporary table. In this case, MySQL generates a warning and sets the algorithm to UNDEFINED. As mentioned earlier, MERGE is handled by merging corresponding parts of a view definition into the statement that refers to the view. The following examples briefly illustrate how the MERGE algorithm works. The examples assume that there is a view v_merge that has this definition: CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS SELECT c1, c2 FROM t WHERE c3 > 100; Example 1: Suppose that we issue this statement: SELECT * FROM v_merge; MySQL handles the statement as follows: o v_merge becomes t o * becomes vc1, vc2, which corresponds to c1, c2 o The view WHERE clause is added The resulting statement to be executed becomes: SELECT c1, c2 FROM t WHERE c3 > 100; Example 2: Suppose that we issue this statement: SELECT * FROM v_merge WHERE vc1 < 100; This statement is handled similarly to the previous one, except that vc1 < 100 becomes c1 < 100 and the view WHERE clause is added to the statement WHERE clause using an AND connective (and parentheses are added to make sure the parts of the clause are executed with correct precedence). The resulting statement to be executed becomes: SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100); Effectively, the statement to be executed has a WHERE clause of this form: WHERE (select WHERE) AND (view WHERE) The MERGE algorithm requires a one-to-one relationship between the rows in the view and the rows in the underlying table. If this relationship does not hold, a temporary table must be used instead. Lack of a one-to-one relationship occurs if the view contains any of a number of constructs: o Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth) o DISTINCT o GROUP BY o HAVING o UNION or UNION ALL o Refers only to literal values (in this case, there is no underlying table) Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view non-updatable. To be more specific, a view is not updatable if it contains any of the following: o Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth) o DISTINCT o GROUP BY o HAVING o UNION or UNION ALL o Subquery in the select list o Join o Non-updatable view in the FROM clause o A subquery in the WHERE clause that refers to a table in the FROM clause o Refers only to literal values (in this case, there is no underlying table to update) o ALGORITHM = TEMPTABLE (use of a temporary table always makes a view non-updatable) With respect to insertability (being updatable with INSERT statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns: o There must be no duplicate view column names. o The view must contain all columns in the base table that do not have a default value. o The view columns must be simple column references and not derived columns. A derived column is one that is not a simple column reference but is derived from an expression. These are examples of derived columns: 3.14159 col1 + 3 UPPER(col2) col3 / col4 (subquery) A view that has a mix of simple column references and derived columns is not insertable, but it can be updatable if you update only those columns that are not derived. Consider this view: CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t; This view is not insertable because col2 is derived from an expression. But it is updatable if the update does not try to update col2. This update is allowable: UPDATE v SET col1 = 0; This update is not allowable because it attempts to update a derived column: UPDATE v SET col2 = 0; It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with the MERGE algorithm. For this to work, the view must use an inner join (not an outer join or a UNION). Also, only a single table in the view definition can be updated, so the SET clause must name only columns from one of the tables in the view. Views that use UNION ALL are disallowed even though they might be theoretically updatable, because the implementation uses temporary tables to process them. For a multiple-table updatable view, INSERT can work if it inserts into a single table. DELETE is not supported. The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true. create-viewH"TRIM!+Syntax: TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str) Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed. mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' string-functionsQ"ISSyntax: IS boolean_value, IS NOT boolean_value Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN. mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; -> 1, 1, 1 mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN; -> 1, 1, 0 comparison-operators" GET_FORMATSyntax: GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL') Returns a format string. This function is useful in combination with the DATE_FORMAT() and the STR_TO_DATE() functions. mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')); -> '03.10.2003' mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA')); -> '2003-10-31' date-and-time-functionsl2TINYBLOBETINYBLOB A BLOB column with a maximum length of 255 (28 - 1) bytes. string-type-overview82 SAVEPOINTSyntax: SAVEPOINT identifier ROLLBACK [WORK] TO SAVEPOINT identifier RELEASE SAVEPOINT identifier InnoDB supports the SQL statements SAVEPOINT and ROLLBACK TO SAVEPOINT. Starting from MySQL 5.0.3, RELEASE SAVEPOINT and the optional WORK keyword for ROLLBACK are supported as well. savepoints"USERgSyntax: USER() Returns the current MySQL username and hostname as a string in the utf8 character set. 4mysql> SELECT USER(); -> 'davida@localhost' information-functions2MPOINTFROMWKBMPointFromWKB(wkb[,srid]), MultiPointFromWKB(wkb[,srid]) Constructs a MULTIPOINT value using its WKB representation and SRID. gis-wkb-functions-2 ALTER TABLE$ Syntax: ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition,...) | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) | ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...) | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | table_option ... ALTER TABLE enables you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and type of the table. alter-table"PURGE MASTER LOGS(Syntax: PURGE {MASTER | BINARY} LOGS TO 'log_name' PURGE {MASTER | BINARY} LOGS BEFORE 'date' Deletes all the binary logs listed in the log index prior to the specified log or date. The logs also are removed from the list recorded in the log index file, so that the given log becomes the first. VPURGE MASTER LOGS TO 'mysql-bin.010'; PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26'; purge-master-logs2 CHAR BYTE_The CHAR BYTE data type is an alias for the BINARY data type. This is a compatibility feature. string-type-overview2 REPAIR TABLESyntax: REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM] REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk --recover tbl_name. REPAIR TABLE works for MyISAM and for ARCHIVE tables. See [myisam-storage-engine], and [archive-storage-engine]. This statement requires SELECT and INSERT privileges for the table. repair-tablet"MERGE$.The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. "Identical" means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order. However, any or all of the MyISAM tables can be compressed with myisampack. See [myisampack]. Differences in table options such as AVG_ROW_LENGTH, MAX_ROWS, or PACK_KEYS do not matter. mysql> CREATE TABLE t1 ( -> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> message CHAR(20)) ENGINE=MyISAM; mysql> CREATE TABLE t2 ( -> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> message CHAR(20)) ENGINE=MyISAM; mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1'); mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2'); mysql> CREATE TABLE total ( -> a INT NOT NULL AUTO_INCREMENT, -> message CHAR(20), INDEX(a)) -> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; merge-storage-engine2 CREATE TABLE$ Syntax: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_option ...] Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_option ...] select_statement Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) } create_definition: column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | {INDEX|KEY} [index_name] [index_type] (index_col_name,...) | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | CHECK (expr) column_definition: col_name data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] data_type: TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | YEAR | CHAR(length) [BINARY | ASCII | UNICODE] | VARCHAR(length) [BINARY] | BINARY(length) | VARBINARY(length) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] | TEXT [BINARY] | MEDIUMTEXT [BINARY] | LONGTEXT [BINARY] | ENUM(value1,value2,value3,...) | SET(value1,value2,value3,...) | spatial_type index_col_name: col_name [(length)] [ASC | DESC] reference_definition: REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION table_option: {ENGINE|TYPE} [=] engine_name | AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET charset_name | CHECKSUM [=] {0 | 1} | COLLATE collation_name | COMMENT [=] 'string' | CONNECTION [=] 'connect_string' | DATA DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | INDEX DIRECTORY [=] 'absolute path to directory' | INSERT_METHOD [=] { NO | FIRST | LAST } | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | UNION [=] (tbl_name[,tbl_name]...) select_statement: [IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement) CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table. Rules for allowable table names are given in [legal-names]. By default, the table is created in the default database. An error occurs if the table exists, if there is no default database, or if the database does not exist. create-table]">Syntax: > Greater than: "mysql> SELECT 2 > 2; -> 0 comparison-operatorsX2ANALYZE TABLE3Syntax: ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock. This statement works with MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk --analyze. MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. This statement requires SELECT and INSERT privileges for the table. analyze-table?" MICROSECONDSyntax: MICROSECOND(expr) Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999. mysql> SELECT MICROSECOND('12:00:00.123456'); -> 123456 mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010'); -> 10 date-and-time-functions^" CONSTRAINT$^InnoDB also supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this: [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB; CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=INNODB; innodb-foreign-key-constraints"FIELD!Syntax: FIELD(str,str1,str2,str3,...) Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found. If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double. If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT(). mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 2 mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 0 string-functions"MAKETIMEsSyntax: MAKETIME(hour,minute,second) Returns a time value calculated from the hour, minute, and second arguments. 8mysql> SELECT MAKETIME(12,15,30); -> '12:15:30' date-and-time-functions2"CURDATESyntax: CURDATE() Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context. bmysql> SELECT CURDATE(); -> '1997-12-15' mysql> SELECT CURDATE() + 0; -> 19971215 date-and-time-functions_"MIN MAXSyntax: MIN([DISTINCT] expr), MAX([DISTINCT] expr) Returns the minimum or maximum value of expr. MIN() and MAX() may take a string argument; in such cases they return the minimum or maximum string value. See [mysql-indexes]. The DISTINCT keyword can be used to find the minimum or maximum of the distinct values of expr, however, this produces the same result as omitting DISTINCT. MIN() and MAX() return NULL if there were no matching rows. }mysql> SELECT student_name, MIN(test_score), MAX(test_score) -> FROM student -> GROUP BY student_name; group-by-functions2 SET PASSWORD}Syntax: SET PASSWORD [FOR user] = PASSWORD('some password') The SET PASSWORD statement assigns a password to an existing MySQL user account. With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account. With a FOR clause, this statement sets the password for a specific account on the current server host. Only clients that have the UPDATE privilege for the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the mysql.user table entry. For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this: SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass'); set-passwordK2ENUM(ENUM('value1','value2',...) An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value. An ENUM column can have a maximum of 65,535 distinct values. ENUM values are represented internally as integers. string-type-overview" IF FUNCTIONSyntax: IF(expr1,expr2,expr3) If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used. mysql> SELECT IF(1>2,2,3); -> 3 mysql> SELECT IF(1<2,'yes','no'); -> 'yes' mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no' control-flow-functions"DATABASEbSyntax: DATABASE() Returns the default (current) database name as a string in the utf8 character set. If there is no default database, DATABASE() returns NULL. Within a stored routine, the default database is the database that the routine is associated with, which is not necessarily the same as the database that is the default in the calling context. ,mysql> SELECT DATABASE(); -> 'test' information-functions2 POINTFROMWKBZPointFromWKB(wkb[,srid]) Constructs a POINT value using its WKB representation and SRID. gis-wkb-functions"POWEROSyntax: POW(X,Y), POWER(X,Y) Returns the value of X raised to the power of Y. Nmysql> SELECT POW(2,2); -> 4 mysql> SELECT POW(2,-2); -> 0.25 mathematical-functions"ATANVSyntax: ATAN(X) Returns the arc tangent of X, that is, the value whose tangent is X. fmysql> SELECT ATAN(2); -> 1.1071487177941 mysql> SELECT ATAN(-2); -> -1.1071487177941 mathematical-functions"STRCMP!Syntax: STRCMP(expr1,expr2) STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise. mysql> SELECT STRCMP('text', 'text2'); -> -1 mysql> SELECT STRCMP('text2', 'text'); -> 1 mysql> SELECT STRCMP('text', 'text'); -> 0 string-comparison-functions 52INSERT DELAYED Syntax: INSERT DELAYED ... The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT to complete. This is a common situation when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete. When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread. Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts. Note that INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it. The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost. There are some constraints on the use of DELAYED: o INSERT DELAYED works only with MyISAM, MEMORY, and ARCHIVE tables. See [myisam-storage-engine], [memory-storage-engine], and [archive-storage-engine]. For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM. o INSERT DELAYED should be used only for INSERT statements that specify value lists. The server ignores DELAYED for INSERT ... SELECT or INSERT ... ON DUPLICATE KEY UPDATE statements. o Because the INSERT DELAYED statement returns immediately, before the rows are inserted, you cannot use LAST_INSERT_ID() to get the AUTO_INCREMENT value that the statement might generate. o DELAYED rows are not visible to SELECT statements until they actually have been inserted. o DELAYED is ignored on slave replication servers because it could cause the slave to have different data than the master. insert-delayed}2 MEDIUMTEXTTMEDIUMTEXT A TEXT column with a maximum length of 16,777,215 (224 - 1) characters. string-type-overview"LNWSyntax: LN(X) Returns the natural logarithm of X; that is, the base-e logarithm of X. Wmysql> SELECT LN(2); -> 0.69314718055995 mysql> SELECT LN(-2); -> NULL mathematical-functionsS2SHOW COLLATION,Syntax: SHOW COLLATION [LIKE 'pattern'] The output from SHOW COLLATION includes all available character sets. It takes an optional LIKE clause whose pattern indicates which collation names to match. For example: mysql> SHOW COLLATION LIKE 'latin1%'; +-------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+---------+----+---------+----------+---------+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 0 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +-------------------+---------+----+---------+----------+---------+ show-collation"LOGjSyntax: LOG(X), LOG(B,X) If called with one parameter, this function returns the natural logarithm of X. Ymysql> SELECT LOG(2); -> 0.69314718055995 mysql> SELECT LOG(-2); -> NULL mathematical-functions&2SET SQL_LOG_BINSyntax: SET SQL_LOG_BIN = {0|1} Disables or enables binary logging for the current connection (SQL_LOG_BIN is a session variable) if the client has the SUPER privilege. The statement is refused with an error if the client does not have that privilege. set-sql-log-bin"!=Syntax: <>, != Not equal: mysql> SELECT '.01' <> '0.01'; -> 1 mysql> SELECT .01 <> '0.01'; -> 0 mysql> SELECT 'zapp' <> 'zappp'; -> 1 comparison-operators"WHILE#|Syntax: [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label] The statement list within a WHILE statement is repeated as long as the search_condition is true. statement_list consists of one or more statements. A WHILE statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same. ~CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO ... SET v1 = v1 - 1; END WHILE; END while-statement" AES_DECRYPT Syntax: AES_ENCRYPT(str,key_str), AES_DECRYPT(crypt_str,key_str) These functions allow encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as "Rijndael." Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is secure enough for most purposes. AES_ENCRYPT() encrypts a string and returns a binary string. AES_DESCRIPT() descrypts the encrypted string and returns the original string. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL. Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated using this formula: 16 × (trunc(string_length / 16) + 1) If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid. You can use the AES functions to store data in an encrypted form by modifying your queries: 9INSERT INTO t VALUES (1,AES_ENCRYPT('text','password')); encryption-functions"DAYNAMEASyntax: DAYNAME(date) Returns the name of the weekday for date. ;mysql> SELECT DAYNAME('1998-02-05'); -> 'Thursday' date-and-time-functions5" COERCIBILITY\Syntax: COERCIBILITY(str) Returns the collation coercibility value of the string argument. mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci); -> 0 mysql> SELECT COERCIBILITY(USER()); -> 3 mysql> SELECT COERCIBILITY('abc'); -> 4 information-functions2INTINT[(M)] [UNSIGNED] [ZEROFILL] A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. numeric-type-overview"GLENGTH }GLength(ls) Returns as a double-precision number the length of the LineString value ls in its associated spatial reference. mysql> SET @ls = 'LineString(1 1,2 2,3 3)'; mysql> SELECT GLength(GeomFromText(@ls)); +----------------------------+ | GLength(GeomFromText(@ls)) | +----------------------------+ | 2.8284271247462 | +----------------------------+ linestring-property-functions"RADIANS}Syntax: RADIANS(X) Returns the argument X, converted from degrees to radians. (Note that Ï€ radians equals 180 degrees.) 6mysql> SELECT RADIANS(90); -> 1.5707963267949 mathematical-functions" COLLATIONFSyntax: COLLATION(str) Returns the collation of the string argument. mysql> SELECT COLLATION('abc'); -> 'latin1_swedish_ci' mysql> SELECT COLLATION(_utf8'abc'); -> 'utf8_general_ci' information-functions"COALESCEtSyntax: COALESCE(value,...) Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. emysql> SELECT COALESCE(NULL,1); -> 1 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL comparison-operators"VERSIONuSyntax: VERSION() Returns a string that indicates the MySQL server version. The string uses the utf8 character set. 6mysql> SELECT VERSION(); -> '5.0.23-standard' information-functions_"MAKE_SET!2Syntax: MAKE_SET(bits,str1,str2,...) Returns a set value (a string containing substrings separated by `,' characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result. mysql> SELECT MAKE_SET(1,'a','b','c'); -> 'a' mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world'); -> 'hello,world' mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world'); -> 'hello' mysql> SELECT MAKE_SET(0,'a','b','c'); -> '' string-functions" FIND_IN_SET!ASyntax: FIND_IN_SET(str,strlist) Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (`,') character. 7mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2 string-functions