# Integer constants that fit into 32bit range
|
SELECT 1;
|
|
# Boolean predicates
|
SELECT TRUE IS TRUE;
|
SELECT NULL IS NULL;
|
SELECT 1<2;
|
SELECT 'a' LIKE 'a';
|
SELECT 'a' RLIKE 'a';
|
SELECT 'b' BETWEEN 'a' AND 'c';
|
SELECT 'a' IN ('a','b','c');
|
SELECT TRUE AND TRUE;
|
|
|
# Geometry relations
|
# ST_CONTAINS, ST_CROSSES, ST_DISJOINT, ST_EQUALS, ST_INTERSECTS,
|
# ST_OVERLAPS, ST_TOUCHES, ST_WITHIN, ST_RELATE
|
SELECT ST_CONTAINS(POINT(1,1),POINT(1,1));
|
|
# Geometry boolean properties (both with and without the ST_ prefix):
|
# ST_ISEMPTY, ST_ISSIMPLE, ST_ISRING, ST_ISCLOSED
|
SELECT ST_ISEMPTY(POINT(1,1));
|
|
# Geometry numeric properties:
|
# ST_DIMENSION, ST_NUMGEOMETRIES, ST_NUMINTERIOSRING, ST_NUMPOINTS, ST_SRID
|
|
# IP predicates
|
# IS_IPV4, IS_IPV4_COMPAT, IS_IPV6, IS_IPV6_COMPAT
|
|
# JSON predicates
|
# JSON_VALID, JSON_EXISTS, JSON_CONTAINS, JSON_CONTAINS_PATH
|
|
# JSON numeric properties
|
# JSON_LENGTH, JSON_DEPTH
|
|
# Cursor boolean properties (sql_mode=ORACLE)
|
# cursor%FOUND, cursor%NOTFOUND, cursor%ISOPEN
|
|
|
# LENGTH functions
|
# LENGTH, OCTET_LENGTH, CHAR_LENGTH, UNCOMPRESSED_LENGTH
|
SELECT LENGTH('a');
|
|
# Temporal unit extraction functions
|
# TO_DAYS, DAYOFMONTH, DAYOFYEAR, QUARTER, YEAR
|
# HOUR, MINUTE, SECOND, MICROSECOND
|
# WEEK, YEARWEEK
|
# EXTRACT - for a certain set of units
|
SELECT WEEK('2001-01-01');
|
SELECT YEARWEEK('2001-01-01');
|
|
# Date period functions
|
SELECT PERIOD_ADD(200915,13);
|
SELECT PERIOD_DIFF(200915,200811);
|
|
|
# Miscelaneous functions
|
# strcmp
|
# crc32
|
# interval
|
# regexp_instr
|
# connection_id
|
# sign
|
# coercibility
|
# locate
|
# field
|
# ascii
|
# ord
|
# find_in_set
|
# bit_count
|
# benchmark
|
# sleep
|
# get_lock
|
# release_lock
|
# Item_master_gtid_wait
|
# is_free_lock
|
# is_used_lock
|
# sqlcode
|
# name_const
|
|
SELECT STRCMP('a','b');
|
SELECT CRC32('x');
|
SELECT NAME_CONST('name',1);
|
|
|
# Hybrid functions that use above expressions as arguments:
|
# General purpose:
|
# CASE, COALESCE, IFNULL, IF, NULLIF
|
# Specific to sql_mode=ORACLE
|
# NLV2, DECODE
|
|
SELECT COALESCE(1);
|
|
# Recursive calls for these hybrid functions
|
|
SELECT COALESCE(COALESCE(1));
|
|
# Integer CAST for expressions that fit into 32 bit
|
# CAST(AS SIGNED), CAST(AS UNSIGNED)
|
SELECT CAST(1 AS SIGNED);
|
|
# Integer negation for 32 bit arguments
|
SELECT -1;
|
|
# Integer conversion functions (when the result fits into 32 bit)
|
# ABS, FLOOR, CEILING
|
|
# Integer arithmetic for results that fit into 32 bit
|
|
|
SELECT 1+1, 1*1;
|
SELECT 1 DIV 1, 1 MOD 1;
|
|
# Functions LEAST and GREATEST for 32bit arguments
|
SELECT LEAST(1,2);
|
|
# User variable assignment using a 32bit expression
|
SELECT @a:=10;
|