MySQL Functions

LAST_INSERT_ID Function

The LAST_INSERT_ID functions returns the last id of the column that uses AUTO_INCREMENT for its id. It’s also important to know that this function has be called after an insert or else if you just call it without an insert statement it will give you 0.

SELECT LAST_INSERT_ID();

REPLACE Function

REPLACE function replaces a substring within a string with another string. This is very helpful when updating rows.

REPLACE(str,lookingFor,replacement);

Now we are updating rd to Road in street address

CONCAT Function

The CONCAT function concatenates two or more strings together to a single string. It can be used in SELECT statements to concatenate column values.

SELECT CONCAT(column1,column2, ... ) as alias FROM table_name;

Here is an example of the CONCAT function.

CONCAT_WS Function

The CONCAT_WS concatenates strings or columns together with a separator.

Here is an example of the CONCAT_WS function with a ” ” separator.

CURDATE Function (CURRENT_DATE or CURRENT_DATE() are same to CURDATE)

The CURDATE function returns the current date in the format of ‘YYYY-MM-DD’ for string format.

For numeric format, add +0 like the example below.

The CURDATE function is different from the NOW function. The NOW function returns both date and time of the current time.

DATEDIFF Function

The DATEDIFF function calculates the number of days between two DATE, DATETIME, or TIMESTAMP values. If you pass in a DATETIME or TIMESTAMP, the DATEDIFF function only takes the date part and ignores the time part.

SELECT DATEDIFF(date_expression_1,date_expression_2) FROM table_name;

TIMEDIFF Funciton

The TIMEDIFF calculates the time difference between two TIME or DATETIME values. Note here that these two values must be of the same data type. They must be either both TIME or both DATETIME. It one is TIME and the other is DATETIME, MySQL will return a NULL.

SELECT TIMEDIFF(time_expression_1,time_expression_2) FROM table_name;

The result of TIMEDIFF is of this range -838:59:59 to 838:59:59which is about 35 days worth of time.

Here is an example of TIMEDIFF with TIMEs

Here is an example of TIMEDIFF with DATETIMEs

TIMESTAMPDIFF Function

The TIMESTAMPDIFF function calculates the difference between two DATE or DATETIME values. The calculation is begin_timestamp – end_timestamp. These two values can be of mix data type DATE or DATETIME.

SELECT TIMESTAMPDIFF(unit,begin_timestamp,end_timestamp) FROM table_name;

Unit can be: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

Here is an example of mix formats

A practical example is finding user’s age.

SELECT id, TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) age
FROM users;

DATETIME vs TIMESTAMP

TIMESTAMP has a timezone but DATETIME does not.

---