最後更新: 2022-02-08

目錄

• Cast Functions - BINARY(), CAST()
• String Functions
  - LENGTH()
  - CONCAT()
  - REPLACE()
  - SUBSTRING_INDEX()
• Mathematical Functions
• Arithmetic Operators
• Time Functions
• Setting: group_concat_max_len

---

Cast Functions - BINARY(), CAST()

 

Function

BINARY()    -    Cast a string to a binary string

CAST()       -    Cast a value as a certain type

Compare

force a column comparison to be done byte by byte rather than character by character.

This causes the comparison to be case sensitive even if the column is not defined as BINARY or BLOB.

i.e.

SELECT 'a' = 'A';               # 1

SELECT 'a' = 'a ';              # 1

SELECT BINARY 'a' = 'A';   # 0

The length for BINARY and VARBINARY is a length in bytes

BINARY()

SELECT BINARY('@datahunter.org')

CAST()

CAST(foo AS CHAR(100))

Supported types (5.5) are:

• BINARY[(N)]
• CHAR[(N)]
• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED [INTEGER]
• TIME
• UNSIGNED [INTEGER]

Example

SELECT CAST( email AS CHAR( 255 ) ) FROM  `mailaddr` ;

SELECT CAST( email AS CHAR ) FROM  `mailaddr` ;

 

---

BINARY vs VARBINARY

 

The BINARY and VARBINARY types are similar to CHAR and VARCHAR,

except that they contain binary strings rather than nonbinary strings.

 

---

IFNULL

 

# Return Default value if no row found

i.e.

SELECT IFNULL(NULL, 500);

SELECT IFNULL( (SELECT field1 FROM table WHERE id = 123 LIMIT 1) , 'not found');

 

 

---

String Functions

 

LENGTH(string1)

i.e.

SELECT address, length(`forwarding`) FROM `forwardings` ORDER BY length(`forwarding`) DESC

CONCAT(string1, string2,…)

Example

# If the arguments include any binary strings, the result is a binary string.

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

SELECT CONCAT(field1, '/'), CONCAT(field2, '/'), ... FROM `socials` WHERE 1

 * CONCAT() returns NULL if any argument is NULL.

 

REPLACE(str, find_string, replace_with)

UPDATE `table` SET `field` = REPLACE(`field`, "orig_string", "new_anothervalue")

# 去除 domain, 只保留 username

select REPLACE(email ,"@datahunter.org","") from users

SUBSTRING_INDEX()

Split value from one field to two

Usage:

string SUBSTRING_INDEX(string, delimiter, number)

Number: 1 = 第一個, -1 = 最尾那個

i.e.

SELECT SUBSTRING_INDEX("[email protected]","@",1);

SELECT SUBSTRING_INDEX(username, "@", 1) FROM mailbox;

 

 

---

Mathematical Functions

 

ABS()

i.e.

SELECT ABS(-32);

 

 

---

Arithmetic Operators

 

操作:

• DIV    (Integer division)
• /
• % or MOD
• +
• -
• *

i.e.

SELECT 3+5;
SELECT 10 DIV 3;

 

---

Time Functions

 

Format:

SELECT DATE_FORMAT(CURDATE(), '%d/%m/%Y')

Function:

# 2014-10-27 14:14:58

SELECT NOW();

# 2014-10-27

SELECT CURDATE();

# 14:14:58

SELECT CURTIME();

# Default: 0000-00-00 00:00:00

SELECT UTC_TIME(); -- same as SELECT unix_timestamp(now());

Unix time:

SELECT UNIX_TIMESTAMP(NOW())

SELECT FROM_UNIXTIME(1414391055)

Other time function

• SECOND()  # Return the second (0-59)
• MINUTE()   # Return the minute from the argument
• HOUR()      # Extract the hour
• DATE()       # Extract the date part of a date
• MONTH()    # Return the month from the date passed
• YEAR()       # Return the year

mysql date calculation

ADDDATE() / ADDTIME()

Syntax

ADDDATE(date,INTERVAL expr unit)

mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
        -> '2008-02-02'

# MySQL treats it as an integer number of days to be added to expr.

mysql> SELECT ADDDATE('2008-01-02', 31);
            -> '2008-02-02'

DATE_SUB()

Usage: date_sub(date, INTERVAL N interval)

從 date 減去 INTERVAL

interval:

• SECOND, MINUTE, HOUR
• DAY, WEEK, MONTH, QUARTER, YEAR

應用: 清除 1 年前的 EventLog

DELETE FROM `EventLog` WHERE `created_time` < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

DATEDIFF()

The function returns the time between two dates

Syntax: DATEDIFF(date1,date2)     # date1 - date2

Table auto add create time

Name: timestamp

Type: TIMESTAMP

Default: CURRENT_TIME

CURRENT_TIMESTAMP limit

在 mysql 5.5

CREATE TABLE `booking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifyDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

會有 Error

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

原因

mysql 5.5

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column,

as the auto-update value, or both.

It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

mysql 5.6

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time.

This restriction has been lifted.

Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses.

In addition, these clauses now can be used with DATETIME column definitions.

For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

Example

# To find rows between two dates or timestamps:

SELECT * FROM events
where event_date between '2018-01-01' and '2018-01-31';

# To convert a timestamp to a unix timestamp

SELECT unix_timestamp('2018-01-01 00:00:01');

# To find rows created within the last week:

SELECT * FROM events
WHERE event_date > date_sub(now(), interval 1 week);

 

---

Setting: group_concat_max_len

 

Returns a string result with the concatenated non-NULL values from a group

It returns NULL if there are no non-NULL values.

To eliminate duplicate values, use the DISTINCT clause.

Usage

GROUP_CONCAT([DISTINCT] expr [,expr ...]

[ORDER BY {unsigned_integer | col_name | expr}  [ASC | DESC] [,col_name ...]] 

[SEPARATOR str_val])

Len

# Default: 1024

SET @@group_concat_max_len = value_numeric;

 

---