MySQL数据库

开发平台：

Visual C++

fill_help_tables.sql：源码内容

--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
-- DO NOT EDIT THIS FILE. It is generated automatically.
delete from help_topic;
delete from help_category;
delete from help_keyword;
delete from help_relation;
insert into help_category (help_category_id,name,parent_category_id,url) values
(1,"Geographic",0,''),
(2,"Polygon properties",27,''),
(3,"Column Types",28,''),
(4,"WKT",27,''),
(5,"Numeric Functions",31,''),
(6,"MBR",27,''),
(7,"Control flow functions",31,''),
(8,"Transactions",28,''),
(9,"Point properties",27,''),
(10,"Encryption Functions",31,''),
(11,"LineString properties",27,''),
(12,"Logical operators",31,''),
(13,"Miscellaneous Functions",31,''),
(14,"Functions and Modifiers for Use with GROUP BY",28,''),
(15,"Information Functions",31,'');
insert into help_category (help_category_id,name,parent_category_id,url) values
(16,"Storage Engines",28,''),
(17,"Comparison operators",31,''),
(18,"Bit Functions",31,''),
(19,"Geometry constructors",27,''),
(20,"GeometryCollection properties",1,''),
(21,"Administration",28,''),
(22,"Data Manipulation",28,''),
(23,"Language Structure",28,''),
(24,"Geometry relations",27,''),
(25,"Date and Time Functions",31,''),
(26,"WKB",27,''),
(27,"Geographic Features",28,''),
(28,"Contents",0,''),
(29,"Geometry properties",27,''),
(30,"String Functions",31,'');
insert into help_category (help_category_id,name,parent_category_id,url) values
(31,"Functions",28,''),
(32,"Data Definition",28,'');
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (0,22,"JOIN","MySQL supports the following JOIN syntaxes for the table_references
part of SELECT statements and multiple-table DELETE and UPDATE
statements:
table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ ON table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference
table_reference is defined as:
tbl_name [[AS] alias]
[{USE|IGNORE|FORCE} INDEX (key_list)]
join_condition is defined as:
ON conditional_expr | USING (column_list)
","mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;
","join"),(1,30,"HEX","Syntax:
HEX(N_or_S)
If N_OR_S is a number, returns a string representation of the
hexadecimal value of N, where N is a longlong (BIGINT) number. This is
equivalent to CONV(N,10,16).
From MySQL 4.0.1 and up, if N_OR_S is a string, returns a hexadecimal
string representation of N_OR_S where each character in N_OR_S is
converted to two hexadecimal digits.
","mysql> SELECT HEX(255);
-> 'FF'
mysql> SELECT 0x616263;
-> 'abc'
mysql> SELECT HEX('abc');
-> 616263
","string-functions"),(2,30,"REPLACE","Syntax:
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str
replaced by the string to_str. REPLACE() performs a case-sensitive
match when searching for from_str.
","mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
","string-functions"),(3,30,"REPEAT","Syntax:
REPEAT(str,count)
Returns a string consisting of the string str repeated count times. If
count <= 0, returns an empty string. Returns NULL if str or count are
NULL.
","mysql> SELECT REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
","string-functions"),(4,24,"CONTAINS","Contains(g1,g2)
Returns 1 or 0 to indicate whether or not g1 completely contains g2.
","","functions-that-test-spatial-relationships-between-geometries"),(5,29,"SRID","SRID(g)
Returns an integer indicating the Spatial Reference System ID for the
geometry value g.
In MySQL, the SRID value is just an integer associated with the
geometry value. All calculations are done assuming Euclidean (planar)
geometry.
","mysql> SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
+-----------------------------------------------+
| SRID(GeomFromText('LineString(1 1,2 2)',101)) |
+-----------------------------------------------+
| 101 |
+-----------------------------------------------+
","general-geometry-property-functions"),(6,25,"CURRENT_TIMESTAMP","Syntax:
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().
","","date-and-time-functions"),(7,14,"VARIANCE","Syntax:
VARIANCE(expr)
Returns the population standard variance of expr. This is an extension
to standard SQL, available in MySQL 4.1 or later.
VARIANCE() returns NULL if there were no matching rows.
","","group-by-functions"),(8,30,"CONCAT","Syntax:
CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments.
Returns NULL if any argument is NULL. May have one or more arguments.
If all arguments are non-binary strings, the result is a non-binary
string. If the arguments include any binary strings, the result is a
binary string. A numeric argument is converted to its equivalent binary
string form; if you want to avoid that you can use explicit type cast,
like in this example: SELECT CONCAT(CAST(int_col AS CHAR), char_col)
","mysql> SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
-> NULL
mysql> SELECT CONCAT(14.3);
-> '14.3'
","string-functions"),(9,27,"GEOMETRY HIERARCHY","Geometry is the base class. It's an abstract class. The instantiable
subclasses of Geometry are restricted to zero-, one-, and
two-dimensional geometric objects that exist in two-dimensional
coordinate space. All instantiable geometry classes are defined so that
valid instances of a geometry class are topologically closed (that is,
all defined geometries include their boundary).
The base Geometry class has subclasses for Point, Curve, Surface, and
GeometryCollection:
o Point represents zero-dimensional objects.
o Curve represents one-dimensional objects, and has subclass
LineString, with sub-subclasses Line and LinearRing.
o Surface is designed for two-dimensional objects and has subclass
Polygon.
o GeometryCollection has specialized zero-, one-, and two-dimensional
collection classes named MultiPoint, MultiLineString, and
MultiPolygon for modeling geometries corresponding to collections of
Points, LineStrings, and Polygons, respectively. MultiCurve and
MultiSurface are introduced as abstract superclasses that generalize
the collection interfaces to handle Curves and Surfaces.
Geometry, Curve, Surface, MultiCurve, and MultiSurface are defined as
non-instantiable classes. They define a common set of methods for their
subclasses and are included for extensibility.
Point, LineString, Polygon, GeometryCollection, MultiPoint,
MultiLineString, and MultiPolygon are instantiable classes.
","","gis-geometry-class-hierarchy"),(10,30,"CHAR FUNCTION","Syntax:
CHAR(N,... [USING charset])
CHAR() interprets each argument N as an integer and returns a string
consisting of the characters given by the code values of those
integers. NULL values are skipped.
CHAR() returns a string in the connection character set. As of MySQL
4.1.16, the optional USING clause may be used to produce a string in a
given character set:
mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
+---------------------+--------------------------------+
| latin1 | utf8 |
+---------------------+--------------------------------+
","mysql> SELECT CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
-> 'MMM'
","string-functions"),(11,3,"DATETIME","DATETIME
A date and time combination. The supported range is '1000-01-01
00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in
'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to
DATETIME columns using either strings or numbers.
","","date-and-time-type-overview"),(12,3,"INTEGER","INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This is a synonym for INT.
","","numeric-type-overview"),(13,30,"LOWER","Syntax:
LOWER(str)
Returns the string str with all characters changed to lowercase
according to the current character set mapping (the default is cp1252
Latin1).
","mysql> SELECT LOWER('QUADRATICALLY');
-> 'quadratically'
","string-functions"),(14,25,"MONTH","Syntax:
MONTH(date)
Returns the month for date, in the range 1 to 12.
","mysql> SELECT MONTH('1998-02-03');
-> 2
","date-and-time-functions");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (15,3,"TINYINT","TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned
range is 0 to 255.
","","numeric-type-overview"),(16,13,"MASTER_POS_WAIT","Syntax:
MASTER_POS_WAIT(log_name,log_pos[,timeout])
This function is useful for control of master/slave synchronization. It
blocks until the slave has read and applied all updates up to the
specified position in the master log. The return value is the number of
log events it had to wait for to get to the specified position. The
function returns NULL if the slave SQL thread is not started, the
slave's master information is not initialized, the arguments are
incorrect, or an error occurs. It returns -1 if the timeout has been
exceeded. If the slave SQL thread stops while MASTER_POS_WAIT() is
waiting, the function returns NULL. If the slave is past the specified
position, the function returns immediately.
","","miscellaneous-functions"),(17,30,"REGEXP","Syntax:
expr REGEXP pat expr RLIKE pat
Performs a pattern match of a string expression expr against a pattern
pat. The pattern can be an extended regular expression. The syntax for
regular expressions is discussed in [regexp]. Returns 1 if expr matches
pat; otherwise it returns 0. If either expr or pat is NULL, the result
is NULL. RLIKE is a synonym for REGEXP, provided for mSQL
compatibility.
The pattern need not be a literal string. For example, it can be
specified as a string expression or table column.
Note: Because MySQL uses the C escape syntax in strings (for example,
`\n' to represent the newline character), you must double any `\' that
you use in your REGEXP strings.
As of MySQL 3.23.4, REGEXP is not case sensitive, except when used with
binary strings.
","mysql> SELECT 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> SELECT 'Monty!' REGEXP '.*';
-> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
-> 1 0
mysql> SELECT 'a' REGEXP '^[a-d]';
-> 1
","string-comparison-functions"),(18,18,"^","Syntax:
^
Bitwise XOR:
","mysql> SELECT 1 ^ 1;
-> 0
mysql> SELECT 1 ^ 0;
-> 1
mysql> SELECT 11 ^ 3;
-> 8
","bit-functions"),(19,25,"DATE OPERATIONS","Syntax:
DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type)
These functions perform date arithmetic. date is a DATETIME or DATE
value specifying the starting date. expr is an expression specifying
the interval value to be added or subtracted from the starting date.
expr is a string; it may start with a `-' for negative intervals. type
is a keyword indicating how the expression should be interpreted.
","mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
-> '1998-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
-> '1998-01-01'
mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
-> '1997-12-31 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL 1 SECOND);
-> '1998-01-01 00:00:00'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL 1 DAY);
-> '1998-01-01 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
-> '1998-01-01 00:01:00'
mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
-> INTERVAL '1 1:1:1' DAY_SECOND);
-> '1997-12-30 22:58:59'
mysql> SELECT DATE_ADD('1998-01-01 00:00:00',
-> INTERVAL '-1 10' DAY_HOUR);
-> '1997-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
-> INTERVAL '1.999999' SECOND_MICROSECOND);
-> '1993-01-01 00:00:01.000001'
","date-and-time-functions"),(20,24,"WITHIN","Within(g1,g2)
Returns 1 or 0 to indicate whether or not g1 is spatially within g2.
","","functions-that-test-spatial-relationships-between-geometries"),(21,25,"WEEK","Syntax:
WEEK(date[,mode])
This function returns the week number for date. The two-argument form
of WEEK() allows you to specify whether the week starts on Sunday or
Monday and whether the return value should be in the range from 0 to 53
or from 1 to 53. If the mode argument is omitted, the value of the
default_week_format system variable is used (or 0 before MySQL 4.0.14).
See [server-system-variables].
","mysql> SELECT WEEK('1998-02-20');
-> 7
mysql> SELECT WEEK('1998-02-20',0);
-> 7
mysql> SELECT WEEK('1998-02-20',1);
-> 8
mysql> SELECT WEEK('1998-12-31',1);
-> 53
","date-and-time-functions"),(22,8,"LOCK","Syntax:
LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES
LOCK TABLES locks tables for the current thread. If any of the tables
are locked by other threads, it blocks until all locks can be acquired.
UNLOCK TABLES releases any locks held by the current thread. All tables
that are locked by the current thread are implicitly unlocked when the
thread issues another LOCK TABLES, or when the connection to the server
is closed.
A table lock protects only against inappropriate reads or writes by
other clients. The client holding the lock, even a read lock, can
perform table-level operations such as DROP TABLE.
","","lock-tables"),(23,22,"RESET SLAVE","Syntax:
RESET SLAVE
Makes the slave forget its replication position in the master's binary
logs. This statement is meant to be used for a clean start: It deletes
the master.info and relay-log.info files, all the relay logs, and
starts a new relay log.
Note: All relay logs are deleted, even if they have not been completely
executed by the slave SQL thread. (This is a condition likely to exist
on a replication slave if you have issued a STOP SLAVE statement or if
the slave is highly loaded.)
Connection information stored in the master.info file is immediately
reset using any values specified in the corresponding startup options.
This information includes values such as master host, master port,
master user, and master password. If the slave SQL thread was in the
middle of replicating temporary tables when it was stopped, and RESET
SLAVE is issued, these replicated temporary tables are deleted on the
slave.
This statement was named FLUSH SLAVE before MySQL 3.23.26.
","","reset-slave"),(24,19,"POLYGON","Polygon(ls1,ls2,...)
Constructs a WKB Polygon value from a number of WKB LineString
arguments. If any argument does not represent the WKB of a LinearRing
(that is, not a closed and simple LineString) the return value is NULL.
","","gis-mysql-specific-functions"),(25,25,"MINUTE","Syntax:
MINUTE(time)
Returns the minute for time, in the range 0 to 59.
","mysql> SELECT MINUTE('98-02-03 10:05:03');
-> 5
","date-and-time-functions"),(26,25,"DAY","Syntax:
DAY(date)
DAY() is a synonym for DAYOFMONTH(). It is available as of MySQL 4.1.1.
","","date-and-time-functions"),(27,30,"MID","Syntax:
MID(str,pos,len)
MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).
","","string-functions"),(28,22,"REPLACE INTO","Syntax:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
REPLACE works exactly like INSERT, except that if an old record in the
table has the same value as a new record for a PRIMARY KEY or a UNIQUE
index, the old record is deleted before the new record is inserted. See
[insert].
Note that unless the table has a PRIMARY KEY or UNIQUE index, using a
REPLACE statement makes no sense. It becomes equivalent to INSERT,
because there is no index to be used to determine whether a new row
duplicates another.
Values for all columns are taken from the values specified in the
REPLACE statement. Any missing columns are set to their default values,
just as happens for INSERT. You cannot refer to values from the current
row and use them in the new row. If you use an assignment such as SET
col_name = col_name + 1, the reference to the column name on the right
hand side is treated as DEFAULT(col_name), so the assignment is
equivalent to SET col_name = DEFAULT(col_name) + 1.
To be able to use REPLACE, you must have both the INSERT and DELETE
privileges for the table.
","","replace"),(29,13,"UUID","Syntax:
UUID()
Returns a Universal Unique Identifier (UUID) generated according to
"DCE 1.1: Remote Procedure Call" (Appendix A) CAE (Common Applications
Environment) Specifications published by The Open Group in October 1997
(Document Number C706,
http://www.opengroup.org/public/pubs/catalog/c706.htm).
A UUID is designed as a number that is globally unique in space and
time. Two calls to UUID() are expected to generate two different
values, even if these calls are performed on two separate computers
that are not connected to each other.
A UUID is a 128-bit number represented by a string of five hexadecimal
numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:
o The first three numbers are generated from a timestamp.
o The fourth number preserves temporal uniqueness in case the timestamp
value loses monotonicity (for example, due to daylight saving time).
o The fifth number is an IEEE 802 node number that provides spatial
uniqueness. A random number is substituted if the latter is not
available (for example, because the host computer has no Ethernet
card, or we do not know how to find the hardware address of an
interface on your operating system). In this case, spatial uniqueness
cannot be guaranteed. Nevertheless, a collision should have very low
probability.
Currently, the MAC address of an interface is taken into account only
on FreeBSD and Linux. On other operating systems, MySQL uses a
randomly generated 48-bit number.
","mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'
","miscellaneous-functions");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (30,19,"LINESTRING","LineString(pt1,pt2,...)
Constructs a WKB LineString value from a number of WKB Point arguments.
If any argument is not a WKB Point, the return value is NULL. If the
number of Point arguments is less than two, the return value is NULL.
","","gis-mysql-specific-functions"),(31,15,"CONNECTION_ID","Syntax:
CONNECTION_ID()
Returns the connection ID (thread ID) for the connection. Every
connection has its own unique ID.
","mysql> SELECT CONNECTION_ID();
-> 23786
","information-functions"),(32,22,"DELETE","Syntax:
Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*] ...]
FROM table_references
[WHERE where_definition]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*] ...]
USING table_references
[WHERE where_definition]
DELETE deletes rows from tbl_name that satisfy the condition given by
where_definition, and returns the number of records deleted.
If you issue a DELETE statement with no WHERE clause, all rows are
deleted. A faster way to do this, when you do not want to know the
number of deleted rows, is to use TRUNCATE TABLE. See [truncate].
","","delete"),(33,5,"ROUND","Syntax:
ROUND(X) ROUND(X,D)
Returns the argument X, rounded to the nearest integer. With two
arguments, returns X rounded to D decimals. D can be negative in order
to round D digits left of the decimal point of the value X.
","mysql> SELECT ROUND(-1.23);
-> -1
mysql> SELECT ROUND(-1.58);
-> -2
mysql> SELECT ROUND(1.58);
-> 2
mysql> SELECT ROUND(1.298, 1);
-> 1.3
mysql> SELECT ROUND(1.298, 0);
-> 1
mysql> SELECT ROUND(23.298, -1);
-> 20
","mathematical-functions"),(34,7,"NULLIF","Syntax:
NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is
the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
","mysql> SELECT NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1
","control-flow-functions"),(35,22,"STOP SLAVE","Syntax:
STOP SLAVE [thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD
Stops the slave threads. STOP SLAVE requires the SUPER privilege.
Like START SLAVE, as of MySQL 4.0.2, this statement may be used with
the IO_THREAD and SQL_THREAD options to name the thread or threads to
be stopped.
","","stop-slave"),(36,25,"TIMEDIFF","Syntax:
TIMEDIFF(expr,expr2)
TIMEDIFF() returns the time between the start time expr and the end
time expr2. expr and expr2 are time or date-and-time expressions, but
both must be of the same type.
","mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
-> '2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
-> '1997-12-30 01:01:01.000002');
-> '46:58:57.999999'
","date-and-time-functions"),(37,4,"LINEFROMTEXT","LineFromText(wkt[,srid]) , LineStringFromText(wkt[,srid])
Constructs a LINESTRING value using its WKT representation and SRID.
","","gis-wkt-functions"),(38,22,"SHOW MASTER STATUS","Provides status information on the binary log files of the master.
Example:
mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+
","","show-master-status"),(39,25,"ADDTIME","Syntax:
ADDTIME(expr,expr2)
ADDTIME() adds expr2 to expr and returns the result. expr is a time or
datetime expression, and expr2 is a time expression.
","mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999',
-> '1 1:1:1.000002');
-> '1998-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
-> '03:00:01.999997'
","date-and-time-functions"),(40,27,"SPATIAL","MySQL can create spatial indexes using syntax similar to that for
creating regular indexes, but extended with the SPATIAL keyword.
Spatial columns that are indexed currently must be declared NOT NULL.
The following examples demonstrate how to create spatial indexes.
o With CREATE TABLE:
mysql> CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
o With ALTER TABLE:
mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);
o With CREATE INDEX:
mysql> CREATE SPATIAL INDEX sp_index ON geom (g);
To drop spatial indexes, use ALTER TABLE or DROP INDEX:
o With ALTER TABLE:
mysql> ALTER TABLE geom DROP INDEX g;
o With DROP INDEX:
mysql> DROP INDEX sp_index ON geom;
Example: Suppose that a table geom contains more than 32,000
geometries, which are stored in the column g of type GEOMETRY. The
table also has an AUTO_INCREMENT column fid for storing object ID
values.
","","creating-spatial-indexes"),(41,30,"UPPER","Syntax:
UPPER(str)
Returns the string str with all characters changed to uppercase
according to the current character set mapping (the default is cp1252
Latin1).
","mysql> SELECT UPPER('Hej');
-> 'HEJ'
","string-functions"),(42,25,"FROM_UNIXTIME","Syntax:
FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)
Returns a representation of the unix_timestamp argument 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.
If format is given, the result is formatted according to the format
string. format may contain the same specifiers as those listed in the
entry for the DATE_FORMAT() function.
","mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
-> '%Y %D %M %h:%i:%s %x');
-> '2003 6th August 06:22:58 2003'
","date-and-time-functions"),(43,3,"MEDIUMBLOB","MEDIUMBLOB
A BLOB column with a maximum length of 16,777,215 (224 - 1) bytes.
","","string-type-overview"),(44,7,"IFNULL","Syntax:
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns
expr2. IFNULL() returns a numeric or string value, depending on the
context in which it is used.
","mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'
","control-flow-functions");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (45,17,"LEAST","Syntax:
LEAST(value1,value2,...)
With two or more arguments, returns the smallest (minimum-valued)
argument. The arguments are compared using the following rules:
o If the return value is used in an INTEGER context or all arguments
are integer-valued, they are compared as integers.
o If the return value is used in a REAL context or all arguments are
real-valued, they are compared as reals.
o If any argument is a case-sensitive string, the arguments are
compared as case-sensitive strings.
o In all other cases, the arguments are compared as case-insensitive
strings.
","mysql> SELECT LEAST(2,0);
-> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
-> 3.0
mysql> SELECT LEAST('B','A','C');
-> 'A'
","comparison-operators"),(46,17,"=","=
Equal:
","mysql> SELECT 1 = 0;
-> 0
mysql> SELECT '0' = 0;
-> 1
mysql> SELECT '0.0' = 0;
-> 1
mysql> SELECT '0.01' = 0;
-> 0
mysql> SELECT '.01' = 0.01;
-> 1
","comparison-operators"),(47,30,"REVERSE","Syntax:
REVERSE(str)
Returns the string str with the order of the characters reversed.
","mysql> SELECT REVERSE('abc');
-> 'cba'
","string-functions"),(48,17,"ISNULL","Syntax:
ISNULL(expr)
If expr is NULL, ISNULL() returns 1, otherwise it returns 0.
","mysql> SELECT ISNULL(1+1);
-> 0
mysql> SELECT ISNULL(1/0);
-> 1
","comparison-operators"),(49,3,"BINARY","BINARY(M)
The BINARY type is similar to the CHAR type, but stores binary byte
strings rather than non-binary character strings.
This type was added in MySQL 4.1.2.
","","string-type-overview"),(50,3,"BLOB DATA TYPE","A BLOB is a binary large object that can hold a variable amount of
data. The four BLOB types, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB,
differ only in the maximum length of the values they can hold.
","","blob"),(51,29,"BOUNDARY","Boundary(g)
Returns a geometry that is the closure of the combinatorial boundary of
the geometry value g.
","","general-geometry-property-functions"),(52,19,"POINT","Point(x,y)
Constructs a WKB Point using its coordinates.
","","gis-mysql-specific-functions"),(53,15,"CURRENT_USER","Syntax:
CURRENT_USER, CURRENT_USER()
Returns the username and hostname combination that the current session
was authenticated as. This value corresponds to the MySQL account that
determines your access privileges.
The value of CURRENT_USER() can differ from the value of USER().
","mysql> SELECT USER();
-> 'davida@localhost'
mysql> SELECT * FROM mysql.user;
ERROR 1044: Access denied for user ''@'localhost' to
database 'mysql'
mysql> SELECT CURRENT_USER();
-> '@localhost'
","information-functions"),(54,30,"LCASE","Syntax:
LCASE(str)
LCASE() is a synonym for LOWER().
","","string-functions"),(55,17,"<=","Syntax:
<=
Less than or equal:
","mysql> SELECT 0.1 <= 2;
-> 1
","comparison-operators"),(56,22,"UPDATE","Syntax:
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
The UPDATE statement updates columns in existing table rows with new
values. The SET clause indicates which columns to modify and the values
they should be given. The WHERE clause, if given, specifies which rows
should be updated. Otherwise, all rows are updated. If the ORDER BY
clause is specified, the rows are updated in the order that is
specified. The LIMIT clause places a limit on the number of rows that
can be updated.
The UPDATE statement supports the following modifiers:
o If you use the LOW_PRIORITY keyword, execution of the UPDATE is
delayed until no other clients are reading from the table.
o If you use the IGNORE keyword, the update statement does not abort
even if errors occur during the update. Rows for which duplicate-key
conflicts occur are not updated. Rows for which columns are updated
to values that would cause data conversion errors are updated to the
closet valid values instead.
","","update"),(57,32,"DROP INDEX","Syntax:
DROP INDEX index_name ON tbl_name
DROP INDEX drops the index named index_name from the table tbl_name. In
MySQL 3.22 or later, DROP INDEX is mapped to an ALTER TABLE statement
to drop the index. See [alter-table]. DROP INDEX does not do anything
prior to MySQL 3.22.
","","drop-index"),(58,30,"MATCH AGAINST","Syntax:
MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY
EXPANSION])
As of MySQL 3.23.23, MySQL has support for full-text indexing and
searching. A full-text index in MySQL is an index of type FULLTEXT.
FULLTEXT indexes can be used only with MyISAM tables; they can be
created from CHAR, VARCHAR, or TEXT columns as part of a CREATE TABLE
statement or added later using ALTER TABLE or CREATE INDEX. For large
datasets, it is much faster to load your data into a table that has no
FULLTEXT index, and then create the index afterwards, than to load data
into a table that has an existing FULLTEXT index.
","mysql> SELECT id, body, MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root') AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body | score |
+----+-------------------------------------+-----------------+
| 4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
| 6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)
","fulltext-search"),(59,5,"ABS","Syntax:
ABS(X)
Returns the absolute value of X.
","mysql> SELECT ABS(2);
-> 2
mysql> SELECT ABS(-32);
-> 32
","mathematical-functions");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (60,26,"POLYFROMWKB","PolyFromWKB(wkb[,srid]) , PolygonFromWKB(wkb[,srid])
Constructs a POLYGON value using its WKB representation and SRID.
","","gis-wkb-functions"),(61,30,"NOT LIKE","Syntax:
expr NOT LIKE pat [ESCAPE 'escape-char']
This is the same as NOT (expr LIKE pat [ESCAPE 'escape-char']).
","","string-comparison-functions"),(62,30,"SPACE","Syntax:
SPACE(N)
Returns a string consisting of N space characters.
","mysql> SELECT SPACE(6);
-> ' '
","string-functions"),(63,6,"MBR DEFINITION","Its MBR (Minimum Bounding Rectangle), or Envelope. This is the bounding
geometry, formed by the minimum and maximum (X,Y) coordinates:
","((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
","gis-class-geometry"),(64,19,"GEOMETRYCOLLECTION","GeometryCollection(g1,g2,...)
Constructs a WKB GeometryCollection. If any argument is not a
well-formed WKB representation of a geometry, the return value is NULL.
","","gis-mysql-specific-functions"),(65,5,"*","Syntax:
*
Multiplication:
","mysql> SELECT 3*5;
-> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
-> 324518553658426726783156020576256.0
mysql> SELECT 18014398509481984*18014398509481984;
-> 0
","arithmetic-functions"),(66,3,"TIMESTAMP","TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01 00:00:00' to partway through the
year 2037.
A TIMESTAMP column is useful for recording the date and time of an
INSERT or UPDATE operation. The first TIMESTAMP column in a table is
automatically set to the date and time of the most recent operation if
you do not assign it a value yourself. You can also set any TIMESTAMP
column to the current date and time by assigning it a NULL value.
In MySQL 4.1, TIMESTAMP is returned as a string with the format
'YYYY-MM-DD HH:MM:SS'. Display widths (used as described in the
following paragraphs) are no longer supported; the display width is
fixed at 19 characters. If you want to obtain the value as a number,
you should add +0 to the timestamp column.
In MySQL 4.0 and earlier, TIMESTAMP values are displayed in
YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on
whether M is 14 (or missing), 12, 8, or 6, but allows you to assign
values to TIMESTAMP columns using either strings or numbers. The M
argument affects only how a TIMESTAMP column is displayed, not storage.
Its values always are stored using four bytes each. From MySQL 4.0.12,
the --new option can be used to make the server behave as in MySQL 4.1.
Note that TIMESTAMP(M) columns where M is 8 or 14 are reported to be
numbers, whereas other TIMESTAMP(M) columns are reported to be strings.
This is just to ensure that you can reliably dump and restore the table
with these types.
Note: The behavior of TIMESTAMP columns changed considerably in MySQL
4.1. For complete information on the differences with regard to this
column type in MySQL 4.1 and later versions (as opposed to MySQL 4.0
and earlier versions), be sure to see [timestamp-pre-4-1] and
[timestamp-4-1].
","","date-and-time-type-overview"),(67,10,"DES_DECRYPT","Syntax:
DES_DECRYPT(crypt_str[,key_str])
Decrypts a string encrypted with DES_ENCRYPT(). On error, this function
returns NULL.
Note that this function works only if MySQL has been configured with
SSL support. See [secure-connections].
If no key_str argument is given, DES_DECRYPT() examines the first byte
of the encrypted string to determine the DES key number that was used
to encrypt the original string, and then reads the key from the DES key
file to decrypt the message. For this to work, the user must have the
SUPER privilege. The key file can be specified with the --des-key-file
server option.
If you pass this function a key_str argument, that string is used as
the key for decrypting the message.
If the crypt_str argument does not appear to be an encrypted string,
MySQL returns the given crypt_str.
","","encryption-functions"),(68,11,"ENDPOINT","EndPoint(ls)
Returns the Point that is the end point of the LineString value ls.
","mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql> SELECT AsText(EndPoint(GeomFromText(@ls)));
+-------------------------------------+
| AsText(EndPoint(GeomFromText(@ls))) |
+-------------------------------------+
| POINT(3 3) |
+-------------------------------------+
","linestring-property-functions"),(69,22,"CACHE INDEX","Syntax:
CACHE INDEX
tbl_index_list [, tbl_index_list] ...
IN key_cache_name
tbl_index_list:
tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]
The CACHE INDEX statement assigns table indexes to a specific key
cache. It is used only for MyISAM tables.
The following statement assigns indexes from the tables t1, t2, and t3
to the key cache named hot_cache:
mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status | OK |
| test.t2 | assign_to_keycache | status | OK |
| test.t3 | assign_to_keycache | status | OK |
+---------+--------------------+----------+----------+
","","cache-index"),(70,30,"COMPRESS","Syntax:
COMPRESS(string_to_compress)
Compresses a string. This function requires MySQL to have been compiled
with a compression library such as zlib. Otherwise, the return value is
always NULL. The compressed string can be uncompressed with
UNCOMPRESS().
","mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
-> 21
mysql> SELECT LENGTH(COMPRESS(''));
-> 0
mysql> SELECT LENGTH(COMPRESS('a'));
-> 13
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
-> 15
","string-functions"),(71,14,"COUNT","Syntax:
COUNT(expr)
Returns a count of the number of non-NULL values in the rows retrieved
by a SELECT statement.
COUNT() returns 0 if there were no matching rows.
","mysql> SELECT student.student_name,COUNT(*)
-> FROM student,course
-> WHERE student.student_id=course.student_id
-> GROUP BY student_name;
","group-by-functions"),(72,30,"INSERT","Syntax:
INSERT(str,pos,len,newstr)
Returns the string str, with the substring beginning at position pos
and len characters long replaced by the string newstr. Returns the
original string if pos is not within the length of the string. Replaces
the rest of the string from position pos is len is not within the
length of the rest of the string. Returns NULL if any argument is null.
","mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
-> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
-> 'QuWhat'
","string-functions"),(73,22,"HANDLER","Syntax:
HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE
The HANDLER statement provides direct access to table storage engine
interfaces. It is available for MyISAM tables as MySQL 4.0.0 and InnoDB
tables as of MySQL 4.0.3.
","","handler"),(74,4,"MLINEFROMTEXT","MLineFromText(wkt[,srid]) , MultiLineStringFromText(wkt[,srid])
Constructs a MULTILINESTRING value using its WKT representation and
SRID.
","","gis-wkt-functions");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (75,26,"GEOMCOLLFROMWKB","GeomCollFromWKB(wkb[,srid]) , GeometryCollectionFromWKB(wkb[,srid])
Constructs a GEOMETRYCOLLECTION value using its WKB representation and
SRID.
","","gis-wkb-functions"),(76,32,"RENAME TABLE","Syntax:
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
This statement renames one or more tables. It was added in MySQL
3.23.23.
The rename operation is done atomically, which means that no other
thread can access any of the tables while the rename is running. For
example, if you have an existing table old_table, you can create
another table new_table that has the same structure but is empty, and
then replace the existing table with the empty one as follows:
","CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;
","rename-table"),(77,3,"BOOLEAN","BOOL, BOOLEAN
These are synonyms for TINYINT(1). The BOOLEAN synonym was added in
MySQL 4.1.0. A value of zero is considered false. Non-zero values are
considered true.
","","numeric-type-overview"),(78,13,"DEFAULT","Syntax:
DEFAULT(col_name)
Returns the default value for a table column.
","mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;
","miscellaneous-functions"),(79,3,"TINYTEXT","TINYTEXT
A TEXT column with a maximum length of 255 (28 - 1) characters.
","","string-type-overview"),(80,21,"OPTIMIZE TABLE","Syntax:
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
OPTIMIZE TABLE should be used if you have deleted a large part of a
table or if you have made many changes to a table with variable-length
rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records
are maintained in a linked list and subsequent INSERT operations reuse
old record positions. You can use OPTIMIZE TABLE to reclaim the unused
space and to defragment the data file.
","","optimize-table"),(81,10,"DECODE","Syntax:
DECODE(crypt_str,pass_str)
Decrypts the encrypted string crypt_str using pass_str as the password.
crypt_str should be a string returned from ENCODE().
","","encryption-functions"),(82,17,"<=>","Syntax:
<=>
NULL-safe equal. This operator performs an equality comparison like the
= operator, but returns 1 rather than NULL if both operands are NULL,
and 0 rather than NULL if one operand is NULL.
","mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
-> 1, NULL, NULL
","comparison-operators"),(83,22,"LOAD DATA FROM MASTER","Syntax:
LOAD DATA FROM MASTER
This command takes a snapshot of the master and copies it to the slave.
It updates the values of MASTER_LOG_FILE and MASTER_LOG_POS so that the
slave starts replicating from the correct position. Any table and
database exclusion rules specified with the --replicate-*-do-* and
--replicate-*-ignore-* options are honored. --replicate-rewrite-db is
not taken into account. This is because a user could, with this option,
set up a non-unique mapping such as --replicate-rewrite-db=db1->db3 and
--replicate-rewrite-db=db2->db3, which would confuse the slave when
loading tables from the master.
Use of this statement is subject to the following conditions:
o It works only with MyISAM tables. Attempting to load a non-MyISAM
table results in the following error:
ERROR 1189 (08S01): Net error reading from master
o It acquires a global read lock on the master while taking the
snapshot, which prevents updates on the master during the load
operation.
If you are loading large tables, you might have to increase the values
of net_read_timeout and net_write_timeout on both the master and slave
servers. See [server-system-variables].
Note that LOAD DATA FROM MASTER does not copy any tables from the mysql
database. This makes it easy to have different users and privileges on
the master and the slave.
The LOAD DATA FROM MASTER statement requires the replication account
that is used to connect to the master to have the RELOAD and SUPER
privileges on the master and the SELECT privilege for all master tables
you want to load. All master tables for which the user does not have
the SELECT privilege are ignored by LOAD DATA FROM MASTER. This is
because the master hides them from the user: LOAD DATA FROM MASTER
calls SHOW DATABASES to know the master databases to load, but SHOW
DATABASES returns only databases for which the user has some privilege.
See [show-databases]. On the slave side, the user that issues LOAD DATA
FROM MASTER should have grants to drop and create the databases and
tables that are copied.
","","load-data-from-master"),(84,22,"RESET","Syntax:
RESET reset_option [, reset_option] ...
The RESET statement is used to clear the state of various server
operations. It also acts as a stronger version of the FLUSH statement.
See [flush].
","","reset"),(85,13,"GET_LOCK","Syntax:
GET_LOCK(str,timeout)
Tries to obtain a lock with a name given by the string str, with a
timeout of timeout seconds. Returns 1 if the lock was obtained
successfully, 0 if the attempt timed out (for example, because another
client has previously locked the name), or NULL if an error occurred
(such as running out of memory or the thread was killed with mysqladmin
kill). If you have a lock obtained with GET_LOCK(), it is released when
you execute RELEASE_LOCK(), execute a new GET_LOCK(), or your
connection terminates (either normally or abnormally).
This function can be used to implement application locks or to simulate
record locks. Names are locked on a server-wide basis. If a name has
been locked by one client, GET_LOCK() blocks any request by another
client for a lock with the same name. This allows clients that agree on
a given lock name to use the name to perform cooperative advisory
locking. But be aware that it also allows a client that is not among
the set of cooperating clients to lock a name, either inadvertently or
deliberately, and thus prevent any of the cooperating clients from
locking that name. One way to reduce the likelihood of this is to use
lock names that are database-specific or application-specific. For
example, use lock names of the form db_name.str or app_name.str.
","mysql> SELECT GET_LOCK('lock1',10);
-> 1
mysql> SELECT IS_FREE_LOCK('lock2');
-> 1
mysql> SELECT GET_LOCK('lock2',10);
-> 1
mysql> SELECT RELEASE_LOCK('lock2');
-> 1
mysql> SELECT RELEASE_LOCK('lock1');
-> NULL
","miscellaneous-functions"),(86,30,"UCASE","Syntax:
UCASE(str)
UCASE() is a synonym for UPPER().
","","string-functions"),(87,22,"SHOW BINLOG EVENTS","Syntax:
SHOW BINLOG EVENTS
[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Shows the events in the binary log. If you do not specify 'log_name',
the first binary log is displayed.
","","show-binlog-events"),(88,26,"MPOLYFROMWKB","MPolyFromWKB(wkb[,srid]) , MultiPolygonFromWKB(wkb[,srid])
Constructs a MULTIPOLYGON value using its WKB representation and SRID.
","","gis-wkb-functions"),(89,22,"DO","Syntax:
DO expr [, expr] ...
DO executes the expressions but does not return any results. This is
shorthand for SELECT expr, ..., but has the advantage that it is
slightly faster when you do not care about the result.
DO is useful mainly with functions that have side effects, such as
RELEASE_LOCK().
","","do");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (90,25,"CURTIME","Syntax:
CURTIME()
Returns the current time as a value in 'HH:MM:SS' or HHMMSS format,
depending on whether the function is used in a string or numeric
context.
","mysql> SELECT CURTIME();
-> '23:50:26'
mysql> SELECT CURTIME() + 0;
-> 235026
","date-and-time-functions"),(91,30,"CHAR_LENGTH","Syntax:
CHAR_LENGTH(str)
Returns the length of the string str, measured in characters. A
multi-byte character counts as a single character. This means that for
a string containing five two-byte characters, LENGTH() returns 10,
whereas CHAR_LENGTH() returns 5.
","","string-functions"),(92,3,"BIGINT","BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to
9223372036854775807. The unsigned range is 0 to 18446744073709551615.
","","numeric-type-overview"),(93,22,"SET","Syntax:
SET variable_assignment [, variable_assignment] ...
variable_assignment:
user_var_name = expr
| [GLOBAL | SESSION] system_var_name = expr
| @@[global. | session.]system_var_name = expr
SET sets different types of variables that affect the operation of the
server or your client. It can be used to assign values to user
variables or system variables.
","","set-option"),(94,3,"DATE","DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL
displays DATE values in 'YYYY-MM-DD' format, but allows you to assign
values to DATE columns using either strings or numbers.
","","date-and-time-type-overview"),(95,30,"CONV","Syntax:
CONV(N,from_base,to_base)
Converts numbers between different number bases. Returns a string
representation of the number N, converted from base from_base to base
to_base. Returns NULL if any argument is NULL. The argument N is
interpreted as an integer, but may be specified as an integer or a
string. The minimum base is 2 and the maximum base is 36. If to_base is
a negative number, N is regarded as a signed number. Otherwise, N is
treated as unsigned. CONV() works with 64-bit precision.
","mysql> SELECT CONV('a',16,2);
-> '1010'
mysql> SELECT CONV('6E',18,8);
-> '172'
mysql> SELECT CONV(-17,10,-18);
-> '-H'
mysql> SELECT CONV(10+'10'+'10'+0xa,10,10);
-> '40'
","string-functions"),(96,25,"EXTRACT","Syntax:
EXTRACT(type FROM date)
The EXTRACT() function uses the same kinds of interval type specifiers
as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather
than performing date arithmetic.
","mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');
-> 20102
mysql> SELECT EXTRACT(MICROSECOND
-> FROM '2003-01-02 10:30:00.00123');
-> 123
","date-and-time-functions"),(97,10,"ENCRYPT","Syntax:
ENCRYPT(str[,salt])
Encrypt str using the Unix crypt() system call. The salt argument
should be a string with two characters. (As of MySQL 3.22.16, salt may
be longer than two characters.) If no salt argument is given, a random
value is used.
","mysql> SELECT ENCRYPT('hello');
-> 'VxuFAJXVARROc'
","encryption-functions"),(98,10,"OLD_PASSWORD","Syntax:
OLD_PASSWORD(str)
OLD_PASSWORD() is available as of MySQL 4.1, when the implementation of
PASSWORD() was changed to improve security. OLD_PASSWORD() returns the
value of the pre-4.1 implementation of PASSWORD(), and is intended to
permit you to reset passwords for any pre-4.1 clients that need to
connect to your version 4.1 MySQL server without locking them out. See
[password-hashing].
","","encryption-functions"),(99,13,"FORMAT","Syntax:
FORMAT(X,D)
Formats the number X to a format like '#,###,###.##', rounded to D
decimals, and returns the result as a string. If D is 0, the result has
no decimal point or fractional part.
","mysql> SELECT FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
-> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
-> '12,332'
","miscellaneous-functions"),(100,12,"||","Syntax:
OR, ||
Logical OR. When both operands are non-NULL, the result is 1 if any
operand is non-zero, and 0 otherwise. With a NULL operand, the result
is 1 if the other operand is non-zero, and NULL otherwise. If both
operands are NULL, the result is NULL.
","mysql> SELECT 1 || 1;
-> 1
mysql> SELECT 1 || 0;
-> 1
mysql> SELECT 0 || 0;
-> 0
mysql> SELECT 0 || NULL;
-> NULL
mysql> SELECT 1 || NULL;
-> 1
","logical-operators"),(101,7,"CASE","Syntax:
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-functions"),(102,30,"BIT_LENGTH","Syntax:
BIT_LENGTH(str)
Returns the length of the string str in bits.
","mysql> SELECT BIT_LENGTH('text');
-> 32
","string-functions"),(103,2,"EXTERIORRING","ExteriorRing(poly)
Returns the exterior ring of the Polygon value poly as a LineString.
","mysql> SET @poly =
-> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';
mysql> SELECT AsText(ExteriorRing(GeomFromText(@poly)));
+-------------------------------------------+
| AsText(ExteriorRing(GeomFromText(@poly))) |
+-------------------------------------------+
| LINESTRING(0 0,0 3,3 3,3 0,0 0) |
+-------------------------------------------+
","polygon-property-functions"),(104,26,"GEOMFROMWKB","GeomFromWKB(wkb[,srid]) , GeometryFromWKB(wkb[,srid])
Constructs a geometry value of any type using its WKB representation
and SRID.
","","gis-wkb-functions");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (105,22,"SHOW SLAVE HOSTS","Syntax:
SHOW SLAVE HOSTS
Displays a list of replication slaves currently registered with the
master. Any slave not started with the --report-host=slave_name option
is not visible in this list.
","","show-slave-hosts"),(106,8,"START TRANSACTION","Syntax:
START TRANSACTION | BEGIN [WORK]
COMMIT
ROLLBACK
SET AUTOCOMMIT = {0 | 1}
The START TRANSACTION or BEGIN statement begin a new transaction.
COMMIT commits the current transaction, making its changes permanent.
ROLLBACK rolls back the current transaction, canceling its changes. The
SET AUTOCOMMIT statement disables or enables the default autocommit
mode for the current connection.
By default, MySQL runs with autocommit mode enabled. This means that as
soon as you execute a statement that updates (modifies) a table, MySQL
stores the update on disk.
If you are using a transaction-safe storage engine (like InnoDB, BDB or
NDB Cluster), you can disable autocommit mode with the following
statement:
SET AUTOCOMMIT=0;
After disabling autocommit mode by setting the AUTOCOMMIT variable to
zero, you must use COMMIT to store your changes to disk or ROLLBACK if
you want to ignore the changes you have made since the beginning of
your transaction.
If you want to disable autocommit mode for a single series of
statements, you can use the START TRANSACTION statement:
","START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
","commit"),(107,17,"BETWEEN AND","Syntax:
expr BETWEEN min AND max
If expr is greater than or equal to min and expr is less than or equal
to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent
to the expression (min <= expr AND expr <= max) if all the arguments
are of the same type. Otherwise type conversion takes place according
to the rules described at the beginning of this section, but applied to
all the three arguments. Note: Before MySQL 4.0.5, arguments were
converted to the type of expr instead.
","mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
-> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
-> 0
","comparison-operators"),(108,19,"MULTIPOLYGON","MultiPolygon(poly1,poly2,...)
Constructs a WKB MultiPolygon value from a set of WKB Polygon
arguments. If any argument is not a WKB Polygon, the return value is
NULL.
","","gis-mysql-specific-functions"),(109,25,"TIME_FORMAT","Syntax:
TIME_FORMAT(time,format)
This is used like the DATE_FORMAT() function, but the format string may
contain only those format specifiers that handle hours, minutes, and
seconds. Other specifiers produce a NULL value or 0.
","mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
-> '100 100 04 04 4'
","date-and-time-functions"),(110,30,"LEFT","Syntax:
LEFT(str,len)
Returns the leftmost len characters from the string str.
","mysql> SELECT LEFT('foobarbar', 5);
-> 'fooba'
","string-functions"),(111,21,"FLUSH QUERY CACHE","You can defragment the query cache to better utilize its memory with
the FLUSH QUERY CACHE statement. The statement does not remove any
queries from the cache.
The RESET QUERY CACHE statement removes all query results from the
query cache. The FLUSH TABLES statement also does this.
","","query-cache-status-and-maintenance"),(112,3,"SET DATA TYPE","SET('value1','value2',...)
A set. A string object that can have zero or more values, each of which
must be chosen from the list of values 'value1', 'value2', ... A SET
column can have a maximum of 64 members. SET values are represented
internally as integers.
","","string-type-overview"),(113,5,"RAND","Syntax:
RAND() RAND(N)
Returns a random floating-point value v between 0 and 1 inclusive (that
is, in the range 0 <= v <= 1.0). If an integer argument N is specified,
it is used as the seed value, which produces a repeatable sequence.
","mysql> SELECT RAND();
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881
mysql> SELECT RAND(20);
-> 0.15888261251047
","mathematical-functions"),(114,30,"RPAD","Syntax:
RPAD(str,len,padstr)
Returns the string str, right-padded with the string padstr to a length
of len characters. If str is longer than len, the return value is
shortened to len characters.
","mysql> SELECT RPAD('hi',5,'?');
-> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
-> 'h'
","string-functions"),(115,22,"INSERT INTO","Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
INSERT inserts new rows into an existing table. The INSERT ... VALUES
and INSERT ... SET forms of the statement insert rows based on
explicitly specified values. The INSERT ... SELECT form inserts rows
selected from another table or tables. The INSERT ... VALUES form with
multiple value lists is supported in MySQL 3.22.5 or later. The INSERT
... SET syntax is supported in MySQL 3.22.10 or later. INSERT ...
SELECT is discussed further in [insert-select].
","","insert"),(116,32,"CREATE DATABASE","Syntax:
CREATE DATABASE [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
CREATE DATABASE creates a database with the given name. To use CREATE
DATABASE, you need the CREATE privilege on the database.
","","create-database"),(117,3,"DEC","DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED]
[ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
These are synonyms for DECIMAL. The FIXED synonym was added in MySQL
4.1.0 for compatibility with other servers.
","","numeric-type-overview"),(118,30,"ELT","Syntax:
ELT(N,str1,str2,str3,...)
Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is
less than 1 or greater than the number of arguments. ELT() is the
complement of FIELD().
","mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
","string-functions"),(119,18,"~","Syntax:
~
Invert all bits.
","mysql> SELECT 5 & ~1;
-> 4
","bit-functions");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (120,3,"TEXT","TEXT[(M)]
A TEXT column with a maximum length of 65,535 (216 - 1) characters.
Beginning with MySQL 4.1, an optional length M can be given. MySQL will
create the column as the smallest TEXT type largest enough to hold
values M characters long.
","","string-type-overview"),(121,30,"CONCAT_WS","Syntax:
CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() stands for CONCAT With Separator and is a special form of
CONCAT(). The first argument is the separator for the rest of the
arguments. The separator is added between the strings to be
concatenated. The separator can be a string as can the rest of the
arguments. If the separator is NULL, the result is NULL. The function
skips any NULL values after the separator argument.
","mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
-> 'First name,Last Name'
","string-functions"),(122,5,"ASIN","Syntax:
ASIN(X)
Returns the arc sine of X, that is, the value whose sine is X. Returns
NULL if X is not in the range -1 to 1.
","mysql> SELECT ASIN(0.2);
-> 0.201358
mysql> SELECT ASIN('foo');
-> 0.000000
","mathematical-functions"),(123,31,"FUNCTION","Syntax:
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL}
SONAME shared_library_name
DROP FUNCTION function_name
A user-defined function (UDF) is a way to extend MySQL with a new
function that works like a native (built-in) MySQL function such as
ABS() or CONCAT().
function_name is the name that should be used in SQL statements to
invoke the function. The RETURNS clause indicates the type of the
function's return value. shared_library_name is the basename of the
shared object file that contains the code that implements the function.
The file must be located in a directory that is searched by your
system's dynamic linker.
To create a function, you must have the INSERT and privilege for the
mysql database. To drop a function, you must have the DELETE privilege
for the mysql database. This is because CREATE FUNCTION adds a row to
the mysql.func system table that records the function's name, type, and
shared library name, and DROP FUNCTION deletes the function's row from
that table. If you do not have this table, you should run the
mysql_fix_privilege_tables script to create it. See
[upgrading-grant-tables].
","","create-function"),(124,5,"SIGN","Syntax:
SIGN(X)
Returns the sign of the argument as -1, 0, or 1, depending on whether X
is negative, zero, or positive.
","mysql> SELECT SIGN(-32);
-> -1
mysql> SELECT SIGN(0);
-> 0
mysql> SELECT SIGN(234);
-> 1
","mathematical-functions"),(125,25,"SEC_TO_TIME","Syntax:
SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes, and seconds,
as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the
function is used in a string or numeric context.
","mysql> SELECT SEC_TO_TIME(2378);
-> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
-> 3938
","date-and-time-functions"),(126,3,"FLOAT","FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Allowable values are
-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to
3.402823466E+38. These are the theoretical limits, based on the IEEE
standard. The actual range might be slightly smaller depending on your
hardware or operating system.
M is the total number of decimal digits and D is the number of digits
following the decimal point. If M and D are omitted, values are stored
to the limits allowed by the hardware. A single-precision
floating-point number is accurate to approximately 7 decimal places.
If UNSIGNED is specified, negative values are disallowed.
Using FLOAT might give you some unexpected problems because all
calculations in MySQL are done with double precision. See
[no-matching-rows].
","","numeric-type-overview"),(127,30,"LOCATE","Syntax:
LOCATE(substr,str) , LOCATE(substr,str,pos)
The first syntax returns the position of the first occurrence of
substring substr in string str. The second syntax returns the position
of the first occurrence of substring substr in string str, starting at
position pos. Returns 0 if substr is not in str.
","mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
mysql> SELECT LOCATE('bar', 'foobarbar',5);
-> 7
","string-functions"),(128,15,"CHARSET","Syntax:
CHARSET(str)
Returns the character set of the string argument.
","mysql> SELECT CHARSET('abc');
-> 'latin1'
mysql> SELECT CHARSET(CONVERT('abc' USING utf8));
-> 'utf8'
mysql> SELECT CHARSET(USER());
-> 'utf8'
","information-functions"),(129,25,"SUBDATE","Syntax:
SUBDATE(date,INTERVAL expr type) SUBDATE(expr,days)
When invoked with the INTERVAL form of the second argument, SUBDATE()
is a synonym for DATE_SUB(). For information on the INTERVAL argument,
see the discussion for DATE_ADD().
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
As of MySQL 4.1.1, the second syntax is allowed, where expr is a date
or datetime expression and days is the number of days to be subtracted
from expr.
mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);
-> '1997-12-02 12:00:00'
Note that you cannot use format "%X%V" to convert a year-week string to
a date as the combination of a year and week does not uniquely identify
a year and month if the week crosses a month boundary. To convert a
year-week to a date, then you should also specify the weekday:
mysql> select str_to_date('200442 Monday', '%X%V %W');
-> 2004-10-18
","","date-and-time-functions"),(130,25,"DAYOFYEAR","Syntax:
DAYOFYEAR(date)
Returns the day of the year for date, in the range 1 to 366.
","mysql> SELECT DAYOFYEAR('1998-02-03');
-> 34
","date-and-time-functions"),(131,5,"%","Syntax:
MOD(N,M) , N % M N MOD M
Modulo operation. Returns the remainder of N divided by M.
","mysql> SELECT MOD(234, 10);
-> 4
mysql> SELECT 253 % 7;
-> 1
mysql> SELECT MOD(29,9);
-> 2
mysql> SELECT 29 MOD 9;
-> 2
","mathematical-functions"),(132,3,"LONGTEXT","LONGTEXT
A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 - 1)
characters. Up to MySQL 3.23, the client/server protocol and MyISAM
tables had a limit of 16MB per communication packet or table row. From
MySQL 4.0, the maximum allowed length of LONGTEXT columns depends on
the configured maximum packet size in the client/server protocol and
available memory.
","","string-type-overview"),(133,24,"DISJOINT","Disjoint(g1,g2)
Returns 1 or 0 to indicate whether or not g1 is spatially disjoint from
(does not intersect) g2.
","","functions-that-test-spatial-relationships-between-geometries"),(134,22,"KILL","Syntax:
KILL thread_id
Each connection to mysqld runs in a separate thread. You can see which
threads are running with the SHOW PROCESSLIST statement and kill a
thread with the KILL thread_id statement.
If you have the PROCESS privilege, you can see all threads. If you have
the SUPER privilege, you can kill all threads and statements.
Otherwise, you can see and kill only your own threads and statements.
You can also use the mysqladmin processlist and mysqladmin kill
commands to examine and kill threads.
Note: You cannot use KILL with the Embedded MySQL Server library,
because the embedded server merely runs inside the threads of the host
application. It does not create any connection threads of its own.
","","kill");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (135,4,"ASTEXT","AsText(g)
Converts a value in internal geometry format to its WKT representation
and returns the string result.
","mysql> SET @g = 'LineString(1 1,2 2,3 3)';
mysql> SELECT AsText(GeomFromText(@g));
+--------------------------+
| AsText(GeomFromText(@g)) |
+--------------------------+
| LINESTRING(1 1,2 2,3 3) |
+--------------------------+
","functions-to-convert-geometries-between-formats"),(136,30,"LPAD","Syntax:
LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr to a length
of len characters. If str is longer than len, the return value is
shortened to len characters.
","mysql> SELECT LPAD('hi',4,'??');
-> '??hi'
mysql> SELECT LPAD('hi',1,'??');
-> 'h'
","string-functions"),(137,21,"RESTORE TABLE","Syntax:
RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'
Restores the table or tables from a backup that was made with BACKUP
TABLE. Existing tables are not overwritten; if you try to restore over
an existing table, you get an error. Just as BACKUP TABLE, RESTORE
TABLE currently works only for MyISAM tables. The directory should be
specified as a full pathname.
The backup for each table consists of its .frm format file and .MYD
data file. The restore operation restores those files, then uses them
to rebuild the .MYI index file. Restoring takes longer than backing up
due to the need to rebuild the indexes. The more indexes the table has,
the longer it takes.
","","restore-table"),(138,24,"OVERLAPS","Overlaps(g1,g2)
Returns 1 or 0 to indicate whether or not g1 spatially overlaps g2. The
term spatially overlaps is used if two geometries intersect and their
intersection results in a geometry of the same dimension but not equal
to either of the given geometries.
","","functions-that-test-spatial-relationships-between-geometries"),(139,20,"NUMGEOMETRIES","NumGeometries(gc)
Returns the number of geometries in the GeometryCollection value gc.
","mysql> SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
mysql> SELECT NumGeometries(GeomFromText(@gc));
+----------------------------------+
| NumGeometries(GeomFromText(@gc)) |
+----------------------------------+
| 2 |
+----------------------------------+
","geometrycollection-property-functions"),(140,22,"SET GLOBAL SQL_SLAVE_SKIP_COUNTER","Syntax:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n
Skip the next n events from the master. This is useful for recovering
from replication stops caused by a statement.
This statement is valid only when the slave thread is not running.
Otherwise, it produces an error.
Before MySQL 4.0, omit the GLOBAL keyword from the statement.
","","set-global-sql-slave-skip-counter"),(141,25,"MONTHNAME","Syntax:
MONTHNAME(date)
Returns the full name of the month for date.
","mysql> SELECT MONTHNAME('1998-02-05');
-> 'February'
","date-and-time-functions"),(142,6,"MBREQUAL","MBREqual(g1,g2)
Returns 1 or 0 to indicate whether or not the Minimum Bounding
Rectangles of the two geometries g1 and g2 are the same.
","","relations-on-geometry-mbr"),(143,22,"CHANGE MASTER TO","Syntax:
CHANGE MASTER TO master_def [, master_def] ...
master_def:
MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = count
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
Changes the parameters that the slave server uses for connecting to and
communicating with the master server.
MASTER_USER, MASTER_PASSWORD, MASTER_SSL, MASTER_SSL_CA,
MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY, and
MASTER_SSL_CIPHER provide information to the slave about how to connect
to its master.
The relay log options (RELAY_LOG_FILE and RELAY_LOG_POS) are available
beginning with MySQL 4.0.
The SSL options (MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH,
MASTER_SSL_CERT, MASTER_SSL_KEY, and MASTER_SSL_CIPHER) are available
beginning with MySQL 4.1.1. You can change these options even on slaves
that are compiled without SSL support. They are saved to the
master.info file, but are ignored until you use a server that has SSL
support enabled.
If you don't specify a given parameter, it keeps its old value, except
as indicated in the following discussion. For example, if the password
to connect to your MySQL master has changed, you just need to issue
these statements to tell the slave about the new password:
mysql> STOP SLAVE; -- if replication was running
mysql> CHANGE MASTER TO MASTER_PASSWORD='new3cret';
mysql> START SLAVE; -- if you want to restart replication
There is no need to specify the parameters that do not change (host,
port, user, and so forth).
MASTER_HOST and MASTER_PORT are the hostname (or IP address) of the
master host and its TCP/IP port. Note that if MASTER_HOST is equal to
localhost, then, like in other parts of MySQL, the port may be ignored
(if Unix socket files can be used, for example).
If you specify MASTER_HOST or MASTER_PORT, the slave assumes that the
master server is different than before (even if you specify a host or
port value that is the same as the current value.) In this case, the
old values for the master binary log name and position are considered
no longer applicable, so if you do not specify MASTER_LOG_FILE and
MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and
MASTER_LOG_POS=4 are silently appended to it.
MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the
slave I/O thread should begin reading from the master the next time the
thread starts. If you specify either of them, you cannot specify
RELAY_LOG_FILE or RELAY_LOG_POS. If neither of MASTER_LOG_FILE or
MASTER_LOG_POS are specified, the slave uses the last coordinates of
the slave SQL thread before CHANGE MASTER was issued. This ensures that
replication has no discontinuity, even if the slave SQL thread was late
compared to the slave I/O thread, when you just want to change, say,
the password to use. This safe behavior was introduced starting from
MySQL 4.0.17 and 4.1.1. (Before these versions, the coordinates used
were the last coordinates of the slave I/O thread before CHANGE MASTER
was issued. This caused the SQL thread to possibly lose some events
from the master, thus breaking replication.)
CHANGE MASTER deletes all relay log files and starts a new one, unless
you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay logs
are kept; as of MySQL 4.1.1 the relay_log_purge global variable is set
silently to 0.
CHANGE MASTER TO updates the contents of the master.info and
relay-log.info files.
CHANGE MASTER is useful for setting up a slave when you have the
snapshot of the master and have recorded the log and the offset
corresponding to it. After loading the snapshot into the slave, you can
run CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master',
MASTER_LOG_POS=log_offset_on_master on the slave.
Examples:
mysql> CHANGE MASTER TO
-> MASTER_HOST='master2.mycompany.com',
-> MASTER_USER='replication',
-> MASTER_PASSWORD='bigs3cret',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master2-bin.001',
-> MASTER_LOG_POS=4,
-> MASTER_CONNECT_RETRY=10;
mysql> CHANGE MASTER TO
-> RELAY_LOG_FILE='slave-relay-bin.006',
-> RELAY_LOG_POS=4025;
","","change-master-to"),(144,32,"DROP DATABASE","Syntax:
DROP DATABASE [IF EXISTS] db_name
DROP DATABASE drops all tables in the database and deletes the
database. Be very careful with this statement! To use DROP DATABASE,
you need the DROP privilege on the database.
In MySQL 3.22 or later, you can use the keywords IF EXISTS to prevent
an error from occurring if the database does not exist.
","","drop-database"),(145,25,"TIMESTAMP FUNCTION","Syntax:
TIMESTAMP(expr) , TIMESTAMP(expr,expr2)
With a single argument, this function returns the date or datetime
expression expr as a datetime value. With two arguments, it adds the
time expression expr2 to the date or datetime expression expr and
returns theresult as a datetime value.
","mysql> SELECT TIMESTAMP('2003-12-31');
-> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
-> '2004-01-01 00:00:00'
","date-and-time-functions"),(146,30,"CHARACTER_LENGTH","Syntax:
CHARACTER_LENGTH(str)
CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().
","","string-functions"),(147,22,"SHOW GRANTS","Syntax:
SHOW GRANTS FOR user
This statement lists the GRANT statements that must be issued to
duplicate the privileges that are granted to a MySQL user account.
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
As of MySQL 4.1.2, to list the privileges granted to the account that
you are using to connect to the server, you can use any of the
following statements:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
","","show-grants"),(148,5,"CRC32","Syntax:
CRC32(expr)
Computes a cyclic redundancy check value and returns a 32-bit unsigned
value. The result is NULL if the argument is NULL. The argument is
expected to be a string and (if possible) is treated as one if it is
not.
","mysql> SELECT CRC32('MySQL');
-> 3259397556
","mathematical-functions"),(149,12,"XOR","Syntax:
XOR
Logical XOR. Returns NULL if either operand is NULL. For non-NULL
operands, evaluates to 1 if an odd number of operands is non-zero,
otherwise 0 is returned.
","mysql> SELECT 1 XOR 1;
-> 0
mysql> SELECT 1 XOR 0;
-> 1
mysql> SELECT 1 XOR NULL;
-> NULL
mysql> SELECT 1 XOR 1 XOR 1;
-> 1
","logical-operators");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (150,11,"STARTPOINT","StartPoint(ls)
Returns the Point that is the start point of the LineString value ls.
","mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql> SELECT AsText(StartPoint(GeomFromText(@ls)));
+---------------------------------------+
| AsText(StartPoint(GeomFromText(@ls))) |
+---------------------------------------+
| POINT(1 1) |
+---------------------------------------+
","linestring-property-functions"),(151,4,"MPOLYFROMTEXT","MPolyFromText(wkt[,srid]) , MultiPolygonFromText(wkt[,srid])
Constructs a MULTIPOLYGON value using its WKT representation and SRID.
","","gis-wkt-functions"),(152,21,"GRANT","Syntax:
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]
with_option =
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
The GRANT and REVOKE statements allow system administrators to create
MySQL user accounts and to grant rights to and revoke them from
accounts. GRANT and REVOKE are implemented in MySQL 3.22.11 or later.
For earlier MySQL versions, these statements do nothing.
MySQL account information is stored in the tables of the mysql
database. This database and the access control system are discussed
extensively in [database-administration], which you should consult for
additional details.
If the grant tables hold privilege records that contain mixed-case
database or table names and the lower_case_table_names system variable
is set, REVOKE cannot be used to revoke the privileges. It will be
necessary to manipulate the grant tables directly. (GRANT will not
create such records when lower_case_table_names is set, but such
records might have been created prior to setting the variable.)
Privileges can be granted at several levels:
o Global level
Global privileges apply to all databases on a given server. These
privileges are stored in the mysql.user table. GRANT ALL ON *.* and
REVOKE ALL ON *.* grant and revoke only global privileges.
o Database level
Database privileges apply to all tables in a given database. These
privileges are stored in the mysql.db and mysql.host tables. GRANT
ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke only
database privileges.
o Table level
Table privileges apply to all columns in a given table. These
privileges are stored in the mysql.tables_priv table. GRANT ALL ON
db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke
only table privileges.
o Column level
Column privileges apply to single columns in a given table. These
privileges are stored in the mysql.columns_priv table. When using
REVOKE, you must specify the same columns that were granted.
","","grant"),(153,6,"MBRINTERSECTS","MBRIntersects(g1,g2)
Returns 1 or 0 to indicate whether or not the Minimum Bounding
Rectangles of the two geometries g1 and g2 intersect.
","","relations-on-geometry-mbr"),(154,14,"BIT_OR","Syntax:
BIT_OR(expr)
Returns the bitwise OR of all bits in expr. The calculation is
performed with 64-bit (BIGINT) precision.
","","group-by-functions"),(155,25,"YEARWEEK","Syntax:
YEARWEEK(date), YEARWEEK(date,start)
Returns year and week for a date. The start argument works exactly like
the start argument to WEEK(). The year in the result may be different
from the year in the date argument for the first and the last week of
the year.
","mysql> SELECT YEARWEEK('1987-01-01');
-> 198653
","date-and-time-functions"),(156,17,"NOT BETWEEN","Syntax:
expr NOT BETWEEN min AND max
This is the same as NOT (expr BETWEEN min AND max).
","","comparison-operators"),(157,5,"LOG10","Syntax:
LOG10(X)
Returns the base-10 logarithm of X.
","mysql> SELECT LOG10(2);
-> 0.301030
mysql> SELECT LOG10(100);
-> 2.000000
mysql> SELECT LOG10(-100);
-> NULL
","mathematical-functions"),(158,5,"SQRT","Syntax:
SQRT(X)
Returns the square root of a non-negative number X.
","mysql> SELECT SQRT(4);
-> 2
mysql> SELECT SQRT(20);
-> 4.4721359549996
mysql> SELECT SQRT(-16);
-> NULL
","mathematical-functions"),(159,3,"DECIMAL","DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
An unpacked fixed-point number. Behaves like a CHAR column; "unpacked"
means the number is stored as a string, using one character for each
digit of the value. M is the total number of digits and D is the number
of digits after the decimal point. The decimal point and (for negative
numbers) the `-' sign are not counted in M, although space for them is
reserved. If D is 0, values have no decimal point or fractional part.
The maximum range of DECIMAL values is the same as for DOUBLE, but the
actual range for a given DECIMAL column may be constrained by the
choice of M and D. If D is omitted, the default is 0. If M is omitted,
the default is 10.
If UNSIGNED is specified, negative values are disallowed.
Note: Before MySQL 3.23, the value of M must be large enough to include
the space needed for the sign and the decimal point characters.
","","numeric-type-overview"),(160,20,"GEOMETRYN","GeometryN(gc,n)
Returns the n-th geometry in the GeometryCollection value gc. Geometry
numbers begin at 1.
","mysql> SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
mysql> SELECT AsText(GeometryN(GeomFromText(@gc),1));
+----------------------------------------+
| AsText(GeometryN(GeomFromText(@gc),1)) |
+----------------------------------------+
| POINT(1 1) |
+----------------------------------------+
","geometrycollection-property-functions"),(161,32,"CREATE INDEX","Syntax:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
In MySQL 3.22 or later, CREATE INDEX is mapped to an ALTER TABLE
statement to create indexes. See [alter-table]. The CREATE INDEX
statement does not do anything prior to MySQL 3.22.
","","create-index"),(162,32,"ALTER DATABASE","Syntax:
ALTER DATABASE [db_name]
alter_specification [, alter_specification] ...
alter_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
ALTER DATABASE allows you to change the overall characteristics of a
database. These characteristics are stored in the db.opt file in the
database directory. To use ALTER DATABASE, you need the ALTER privilege
on the database.
","","alter-database"),(163,18,"<<","Syntax:
<<
Shifts a longlong (BIGINT) number to the left.
","mysql> SELECT 1 << 2;
-> 4
","bit-functions"),(164,10,"MD5","Syntax:
MD5(str)
Calculates an MD5 128-bit checksum for the string. The value is
returned as a binary string of 32 hex digits, or NULL if the argument
was NULL. The return value can, for example, be used as a hash key.
","mysql> SELECT MD5('testing');
-> 'ae2b1fca515949e5d54fb22b8ed95575'
","encryption-functions");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (165,17,"<","Syntax:
<
Less than:
","mysql> SELECT 2 < 2;
-> 0
","comparison-operators"),(166,25,"UNIX_TIMESTAMP","Syntax:
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since
'1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP()
is called with a date argument, it returns the value of the argument as
seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a
DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or
YYYYMMDD in local time.
","mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
","date-and-time-functions"),(167,25,"DAYOFMONTH","Syntax:
DAYOFMONTH(date)
Returns the day of the month for date, in the range 1 to 31.
","mysql> SELECT DAYOFMONTH('1998-02-03');
-> 3
","date-and-time-functions"),(168,30,"ASCII","Syntax:
ASCII(str)
Returns the numeric value of the leftmost character of the string str.
Returns 0 if str is the empty string. Returns NULL if str is NULL.
ASCII() works for characters with numeric values from 0 to 255.
","mysql> SELECT ASCII('2');
-> 50
mysql> SELECT ASCII(2);
-> 50
mysql> SELECT ASCII('dx');
-> 100
","string-functions"),(169,5,"DIV","Syntax:
DIV
Integer division. Similar to FLOOR() but safe with BIGINT values.
","mysql> SELECT 5 DIV 2;
-> 2
","arithmetic-functions"),(170,22,"SHOW SLAVE STATUS","Syntax:
SHOW SLAVE STATUS
Provides status information on essential parameters of the slave
threads. If you issue this statement using the mysql client, you can
use a \G statement terminator rather than a semicolon to obtain a more
readable vertical layout:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 3
Master_Log_File: gbichot-bin.005
Read_Master_Log_Pos: 79
Relay_Log_File: gbichot-relay-bin.005
Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 79
Relay_Log_Space: 552
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 8
","","show-slave-status"),(171,27,"GEOMETRY","MySQL provides a standard way of creating spatial columns for geometry
types, for example, with CREATE TABLE or ALTER TABLE. Currently,
spatial columns are supported only for MyISAM tables.
","mysql> CREATE TABLE geom (g GEOMETRY);
Query OK, 0 rows affected (0.02 sec)
","creating-spatial-columns"),(172,11,"NUMPOINTS","NumPoints(ls)
Returns the number of points in the LineString value ls.
","mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql> SELECT NumPoints(GeomFromText(@ls));
+------------------------------+
| NumPoints(GeomFromText(@ls)) |
+------------------------------+
| 3 |
+------------------------------+
","linestring-property-functions"),(173,18,"&","Syntax:
&
Bitwise AND:
","mysql> SELECT 29 & 15;
-> 13
","bit-functions"),(174,25,"LOCALTIMESTAMP","Syntax:
LOCALTIMESTAMP, LOCALTIMESTAMP()
LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW().
","","date-and-time-functions"),(175,25,"ADDDATE","Syntax:
ADDDATE(date,INTERVAL expr type) ADDDATE(expr,days)
When invoked with the INTERVAL form of the second argument, ADDDATE()
is a synonym for DATE_ADD(). The related function SUBDATE() is a
synonym for DATE_SUB(). For information on the INTERVAL argument, see
the discussion for DATE_ADD().
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
-> '1998-02-02'
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
-> '1998-02-02'
As of MySQL 4.1.1, the second syntax is allowed, where expr is a date
or datetime expression and days is the number of days to be added to
expr.
","mysql> SELECT ADDDATE('1998-01-02', 31);
-> '1998-02-02'
","date-and-time-functions"),(176,3,"SMALLINT","SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned
range is 0 to 65535.
","","numeric-type-overview"),(177,3,"DOUBLE PRECISION","DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED]
[ZEROFILL]
These are synonyms for DOUBLE. Exception: If the server SQL mode
includes the REAL_AS_FLOAT option, REAL is a synonym for FLOAT rather
than DOUBLE.
","","numeric-type-overview"),(178,30,"ORD","Syntax:
ORD(str)
If the leftmost character of the string str is a multi-byte character,
returns the code for that character, calculated from the numeric values
of its constituent bytes using this formula:
(1st byte code)
+ (2nd byte code × 256)
+ (3rd byte code × 2562) ...
If the leftmost character is not a multi-byte character, ORD() returns
the same value as the ASCII() function.
","mysql> SELECT ORD('2');
-> 50
","string-functions"),(179,29,"ENVELOPE","Envelope(g)
Returns the Minimum Bounding Rectangle (MBR) for the geometry value g.
The result is returned as a Polygon value.
The polygon is defined by the corner points of the bounding box:
POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
","mysql> SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)')));
+-------------------------------------------------------+
| AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))) |
+-------------------------------------------------------+
| POLYGON((1 1,2 1,2 2,1 2,1 1)) |
+-------------------------------------------------------+
","general-geometry-property-functions");
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (180,13,"IS_FREE_LOCK","Syntax:
IS_FREE_LOCK(str)
Checks whether the lock named str is free to use (that is, not locked).
Returns 1 if the lock is free (no one is using the lock), 0 if the lock
is in use, and NULL on errors (such as incorrect arguments).
","","miscellaneous-functions"),(181,24,"TOUCHES","Touches(g1,g2)
Returns 1 or 0 to indicate whether or not g1 spatially touches g2. Two
geometries spatially touch if the interiors of the geometries do not
intersect, but the boundary of one of the geometries intersects either
the boundary or the interior of the other.
","","functions-that-test-spatial-relationships-between-geometries"),(182,13,"INET_ATON","Syntax:
INET_ATON(expr)
Given the dotted-quad representation of a network address as a string,
returns an integer that represents the numeric value of the address.
Addresses may be 4- or 8-byte addresses.
","mysql> SELECT INET_ATON('209.207.224.40');
-> 3520061480
","miscellaneous-functions"),(183,3,"AUTO_INCREMENT","The AUTO_INCREMENT attribute can be used to generate a unique identity
for new rows:
","CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
","example-auto-increment"),(184,30,"UNCOMPRESS","Syntax:
UNCOMPRESS(string_to_uncompress)
Uncompresses a string compressed by the COMPRESS() function. If the
argument is not a compressed value, the result is NULL. This function
requires MySQL to have been compiled with a compression library such as
zlib. Otherwise, the return value is always NULL.
","mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
-> 'any string'
mysql> SELECT UNCOMPRESS('any string');
-> NULL