Syntax edit
Function names are case insensitive. You can write them as you prefer:
SELECT database() -- ok
SELECT DataBase() -- ok
SELECT DATABASE() -- ok
If the IGNORE_SPACE SQL_MODE is not set, you can not put a space between the function name and the first parenthesis. It would return a 1064 error. IGNORE_SPACE is usually 0. The reason is that the parser is faster if that flag is disabled. So:
SELECT DATABASE () -- usually not accepted
SELECT DATABASE() -- always works fine
However, this restriction only applies to the native MySQL functions. UDFs and stored functions may be written with a space after the name.
You can't use a value calculated in the SELECT clause as a constraint in the WHERE clause (its a chicken & egg problem); the WHERE clause is what determines the values in the SELECT clause. What you want is the HAVING clause which is applied *after* all matching rows have been found.
General functions edit
Type-independent functions.
BENCHMARK(times, expression) edit
Executes expression n times and returns how time it spent. Useful to find bottlenecks in SQL expressions.
SELECT BENCHMARK(10000, 'hello'); -- Treatment in 0.0010 sec
CAST(value AS type) edit
Returns value converted in the specified type.
SELECT CAST(20130101 AS DATE); -- 2013-01-01
CHARSET(string) edit
Returns the CHARACTER SET used by string.
SELECT CHARSET(20130101); -- binary
SHOW CHARACTER SET; -- displays all the different installed CHARACTER SET
COALESCE(value, ...) edit
Returns the first argument which is not NULL. If all arguments are NULL, returns NULL. There must be at least one argument.
SELECT COALESCE(NULL, 'hello', NULL); -- hello
COERCIBILITY(string) edit
Returns the coercibility (between 0 to 5):
SELECT COERCIBILITY('hello'); -- 4
Coercibility[1] | Meaning | Example |
---|---|---|
0 | Explicit collation | Value with COLLATE clause |
1 | No collation | Concatenation of strings with different collations |
2 | Implicit collation | Column value |
3 | System constant | USER() return value |
4 | Coercible | Literal string |
5 | Ignorable | NULL or an expression derived from NULL |
COLLATION(string) edit
Returns the COLLATION used by the string.
SELECT COLLATION('hello'); -- utf8_general_ci
CONNECTION_ID() edit
Returns the id of the current thread.
SELECT CONNECTION_ID(); -- 31
CONVERT(value, type) edit
Returns value converted to the specified type.
SELECT CONVERT ('666', UNSIGNED INTEGER)
CONVERT(string USING charset) edit
Converts the passed string to the specified CHARACTER SET.
SELECT CONVERT ('This is a text' USING utf8)
CURRENT_USER() edit
Returns the username and the hostname used in the current connection.
SELECT CURRENT_USER()
SELECT CURRENT_USER -- it's correct
DATABASE() edit
Returns the current database's name, set with the USE command.
SELECT DATABASE()
FOUND_ROWS() edit
After a SELECT with a LIMIT clause and the SQL_CALC_FOUND_ROWS keyword, you can run another SELECT with the FOUND_ROWS() function. It returns the number of rows found by the previous query if it had no LIMIT clause.
SELECT SQL_CALC_FOUND_ROWS * FROM stats ORDER BY id LIMIT 10 OFFSET 50
SELECT FOUND_ROWS() AS n
GREATEST(value1, value2, ...) edit
Returns the greatest argument passed.
IF(val1, val2, val3) edit
If val1 is TRUE, returns val2. If val1 is FALSE or NULL, returns val3.
IFNULL(val1, val2) edit
If val1 is NULL, returns val2; else, returns val1.
ISNULL(value) edit
If the value passed is NULL returns 1, else returns 0.
INTERVAL(val1, val2, val3, ...) edit
Returns the location of the first argument which is greater than the first one, beginning by zero in the integers in parameter:
SELECT INTERVAL(10, 20, 9, 8, 7); -- 0
SELECT INTERVAL(10, 9, 20, 8, 7); -- 1
SELECT INTERVAL(10, 9, 8, 20, 7); -- 2
SELECT INTERVAL(10, 9, 8, 7, 20); -- 3
NULLIF(val1, val2) edit
If val1 = val2, returns NULL; else, returns val1.
LAST_INSERT_ID() edit
Returns the last inserted ID in AUTO_INCREMENT from the database, which can avoid a SELECT when inserting two records where the second needs a foreign key to the first.
LEAST(value1, value2, ...) edit
Returns the minimum argument passed.
SUBSTR(string, start, size) edit
Cut a string:
SELECT SUBSTR('Hello World!', 7, 5); -- World
Date and time edit
There are plenty of date related functions.[2]
SELECT * FROM mytable
WHERE datetimecol >= (CURDATE() - INTERVAL 1 YEAR) AND
datetimecol < (CURDATE() - INTERVAL 1 YEAR) INTERVAL 1 DAY;
SELECT IF(DAYOFMONTH(CURDATE()) <= 15,
DATE_FORMAT(CURDATE(), '%Y-%m-15'),
DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15
FROM table;
SELECT YEAR('2002-05-10'), MONTH('2002-05-10'), DAYOFMONTH('2002-05-10')
SELECT PurchaseDate FROM table WHERE YEAR(PurchaseDate) <= YEAR(CURDATE())
SELECT columns FROM table
WHERE start_time >= '2004-06-01 10:00:00' AND end_time <= '2004-06-03 18:00:00'
SELECT * FROM t1
WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'
SELECT Start_time, End_time FROM Table
WHERE Start_time >= NOW() - INTERVAL 4 HOUR
SELECT NOW() + INTERVAL 60 SECOND
SELECT UNIX_TIMESTAMP('2007-05-01'); -- 1177970400
SELECT FROM_UNIXTIME(1177970400); -- 2007-05-01 00:00:00
Attention: convert('17/02/2016 15:49:03',datetime)
or convert('17-02-2016 15:49:03',datetime)
gives null, so an insert request replaces it by the same result as now()
. This should be convert('2016-02-17 15:49:03',datetime)
or convert('2016/02/17 15:49:03',datetime)
.
Aggregate functions edit
COUNT(field) edit
If * is given, instead of the name of a field, COUNT() returns the number of rows found by the query. It's commonly used to get the number of rows in a table.
SELECT COUNT(*) FROM `antiques`
If the DISTINCT keyword is used, identical rows are counted only once.
SELECT COUNT(DISTINCT *) FROM `antiques`
If a field name is given, returns the number of non-NULL values.
SELECT COUNT(`cost`) FROM `antiques`
If a field name is given and the DISTINCT keyword is given, returns the number of non-NULL values, and identical values are counted only once.
SELECT COUNT(DISTINCT `cost`) FROM `antiques`
You can count non-NULL values for an expression:
SELECT COUNT(`longitude` + `latitude`) FROM `cities`
This returns the number of rows where longitude and latitude are both non-NULL.
MAX(field) edit
MAX() can be used to get the maximum value for an expression in the rows matching to a query. If no row matches the query, returns NULL.
SELECT MAX(`cost`) FROM `antiques`
SELECT MAX(LENGTH(CONCAT(`first_name`, ' ', `last_name`))) FROM `subscribers`
MIN(field) edit
MIN() can be used to get the minimum value for an expression in the rows matching to a query. If no row matches the query, returns NULL.
SELECT MIN(`cost`) FROM `antiques`
AVG(field) edit
AVG() can be used to get the average value for an expression in the rows matching to a query. If no row matches the query, returns NULL.
SELECT AVG(`cost`) FROM `antiques`
SUM(field) edit
SUM() can be used to get the sum of the values for an expression in the rows matching to a query. If no row matches the query, returns NULL.
If SUM(DISTINCT expression) is used, identical values are added only once. It has been added in MySQL 5.1.
SELECT SUM(`cost`) FROM `antiques`
GROUP_CONCAT(field) edit
GROUP_CONCAT() can be used to concatenate values from all records for a group into a single string separated by a comma by default, or any additional token you like if placed as the optional second parameter.
CREATE TEMPORARY TABLE product (
id INTEGER, product_type VARCHAR(10), product_name VARCHAR(50)
);
INSERT INTO product VALUES
(1, 'mp3', 'iPod'),
(2, 'mp3', 'Zune'),
(3, 'mp3', 'ZEN'),
(4, 'notebook', 'Acer Eee PC'),
(4, 'notebook', 'Everex CloudBook');
SELECT * FROM product;
SELECT product_type, group_concat(product_name)
FROM product
GROUP BY product_type;
SELECT product_type, group_concat(' ', product_name)
FROM product
GROUP BY product_type;
Aggregate bit functions edit
General syntax:
FUNCTION_NAME(''expression'')
These functions calculate expression for each row of the result set and perform the calculation between all the expressions. These are bitwise functions. The precision used is 64 bit.
AND edit
SELECT BIT_AND(ip) FROM log
OR edit
SELECT BIT_OR(ip) FROM log
(returns 0 if there are no rows)
XOR edit
SELECT BIT_XOR(ip) FROM log
(returns 0 if there are no rows)