# MySQL/Language/Functions
## Syntax

Function names are case insensitive. You can write them as you prefer:
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:
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

Type-independent functions.

### BENCHMARK(times, expression)

Executes expression n times and returns how time it spent. Useful to find bottlenecks in SQL expressions.

### CAST(value AS type)

Returns value converted in the specified type.

### CHARSET(string)

Returns the CHARACTER SET used by string.

### COALESCE(value, ...)

Returns the first argument which is not NULL. If all arguments are NULL, returns NULL. There must be at least one argument.

### COERCIBILITY(string)

Returns the coercibility (between 0 to 5):
| Coercibility | 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)

Returns the COLLATION used by the string.

### CONNECTION_ID

Returns the id of the current thread.

### CONVERT(value, type)

Returns value converted to the specified type.

### CONVERT(string USING charset)

Converts the passed string to the specified CHARACTER SET.

### CURRENT_USER

Returns the username and the hostname used in the current connection.

### DATABASE

Returns the current database's name, set with the USE command.

### FOUND_ROWS

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.

### GREATEST(value1, value2, ...)

Returns the greatest argument passed.

### IF(val1, val2, val3)

If val1 is TRUE, returns val2. If val1 is FALSE or NULL, returns val3.

### IFNULL(val1, val2)

If val1 is NULL, returns val2; else, returns val1.

### ISNULL(value)

If the value passed is NULL returns 1, else returns 0.

### INTERVAL(val1, val2, val3, ...)

Returns the location of the first argument which is greater than the first one, beginning by zero in the integers in parameter:

### NULLIF(val1, val2)

If val1 = val2, returns NULL; else, returns val1.

### LAST_INSERT_ID

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, ...)

Returns the minimum argument passed.

### SUBSTR(string, start, size)

Cut a string:

## Date and time

There are plenty of date related functions.
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
### COUNT(field)

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.
If the DISTINCT keyword is used, identical rows are counted only once.
If a field name is given, returns the number of non-NULL values.
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.
You can count non-NULL values for an expression:
This returns the number of rows where longitude and latitude are both non-NULL.

### MAX(field)

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.

### MIN(field)

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.

### AVG(field)

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.

### SUM(field)

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.

### GROUP_CONCAT(field)

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.

### Aggregate bit functions

General syntax:
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.

#### OR

(returns 0 if there are no rows)

#### XOR

(returns 0 if there are no rows)

## References

* ↑ http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_coercibility
* ↑ https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html