MySQL/Language/Functions

From Wikibooks, open books for an open world
< MySQL‎ | Language
Jump to: navigation, search

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-indipendent 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.

LEAST(value1, value2, ...)[edit]

Returns the minimum argument passed.

Date and time[edit]

 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

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 comma or any additional token you like.

 CREATE TEMPORARY TABLE p (
        id INTEGER, ptype VARCHAR(10), pname VARCHAR(50)
    );
 
 INSERT INTO p VALUES 
     (1,'mp3','iPod'),
     (2,'mp3','Zune'),
     (3,'mp3','ZEN'),
     (4,'notebook','Acer Eee PC'),
     (4,'notebook','Everex CloudBook');
 
 SELECT * FROM p;
 
 SELECT ptype,group_concat(pname) 
 FROM p 
 GROUP BY ptype;
 
 SELECT ptype,group_concat(' ',pname) 
 FROM p 
 GROUP BY ptype
 ;

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)

References[edit]