myfce - Mysql's compatibility functions.
Features compatible with Mysql databases are provided. These features enable you to easily migrate to LightDB and reduce the costs of reconfiguring applications. The table below lists features compatible with Mysql databases.
Table F.30. Data type
|
Item |
Overview |
|---|---|
|
LONGTEXT |
LONGTEXT is an alias for TEXT. |
Table F.31. Mathematical Functions
Item | Overview |
|---|---|
TRUNCATE | Truncate to specified number of decimal places |
Table F.32. String Functions
|
Item |
Overview |
|---|---|
|
FIND_IN_SET |
Index (position) of first argument within second argument |
|
LOCATE |
Return the position of the first occurrence of substring |
Table F.33. Date/Time Functions
|
Item |
Overview |
|---|---|
|
DATEDIFF |
Subtract two dates |
Table F.34. Conditional Expressions
|
Item |
Overview |
|---|---|
|
IF |
If/else construct |
Table F.35. Aggregate Functions
|
Item |
Overview |
|---|---|
|
GROUP_CONCAT |
Returns a concatenated, delimited list of string values. |
Table F.36. System Information Functions and Operators
|
Item |
Overview |
|---|---|
|
DATABASE |
Synonym for current_schema() |
The following mathematical functions are supported:
TRUNCATE
Description
Truncate to specified number of decimal places.
Syntax
TRUNCATE(SMALLINT X,INTEGER D) returns INTEGER
TRUNCATE(INTEGER X,INTEGER D) returns INTEGER
TRUNCATE(BIGINT X,INTEGER D) returns BIGINT
TRUNCATE(DOUBLE PRECISION X,INTEGER D) returns DOUBLE PRECISION
TRUNCATE(NUMERIC,INTEGER D) returns NUMERIC
General rules
Returns the number X, truncated to D decimal places.
If D is 0, the result has no decimal point or fractional part.
D can be negative to cause D digits left of the decimal point of the value X to become zero.
Example
In the following example, the result of "1.223" truncated to 1 decimal places is returned.
SELECT TRUNCATE(1.223,1);
truncate
----------
1.2
(1 row)
The following string functions are supported:
FIND_IN_SET
LOCATE
Description
Index (position) of first argument within second argument.
Syntax
FIND_IN_SET(str "any",strlist TEXT) returns INTEGER
General rules
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. strlist is a string composed of substrings separated by , characters.
Returns 0 if str is not in strlist or if strlist is the empty string.
Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.
If first argument is not String Types,the first parameter will be converted to string.In other words,find_in_set(n,strlist) is equivalent to find_in_set(n::text,strlist)
The data type of the return value is integer.
Example
In the following example,position of 'b' within 'a,b,c,d' is returned.
SELECT FIND_IN_SET('b','a,b,c,d');
find_in_set
-------------
2
(1 row)
Description
Return the position of the first occurrence of substring.
Syntax
LOCATE(substr TEXT,str TEXT) returns INTEGER
LOCATE(substr TEXT,str TEXT,POS INTEGER) returns INTEGER
General rules
The first syntax returns the position of the first occurrence of substring substr in string str.
The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos.
Returns 0 if substr is not in str.
Returns NULL if any argument is NULL.
Example
In the following example,position of 'bar' in 'foobarbar', starting at position 5. is returned.
SELECT LOCATE('bar', 'foobarbar', 5);
locate
--------
7
(1 row)
The following date/time functions are supported:
DATEDIFF
Description
Subtract two dates.
Syntax
DATEDIFF(expr1 timestamp,expr2 timestamp)
DATEDIFF(expr1 text,expr2 text)
General rules
expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
Returns NULL if any argument is NULL.
Example
In the following example, 1 is returned because '2007-12-31' minus '2007-12-30' equals 1
SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
datediff
----------
1
(1 row)
The following functions for making comparisons are supported:
IF
Description
If expr1 is TRUE, IF() returns expr2. Otherwise, it returns expr3.
Syntax
IF(expr1,expr2,expr3)
General rules
If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is "text".
If one of expr2 and expr3 is a string, the result type of the IF() function is "text", otherwise it is "numeric".
The following data types can be used in expr2 and expr3:
CHAR
VARCHAR
TEXT
INTEGER
BIGINT
SMALLINT
NUMERIC
REAL
DOUBLE PRECISION
Example
In the following example, 3 is returned because the value of 1 > 2 is false.
SELECT IF(1>2,2,3);
if
----
3
(1 row)
The following aggregation functions are supported:
GROUP_CONCAT
Description
Returns a concatenated, delimited list of string values.
Syntax
GROUP_CONCAT( [DISTINCT] column_name1 [ORDER BY {unsigned_integer | column_name2 | expr} [ASC | DESC] [SEPARATOR delimiter TEXT]) returns TEXT
General rules
This function returns a string result with the concatenated non-NULL values from a group.
It returns NULL if there are no non-NULL values.
To sort values in the result, use the ORDER BY clause.To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword.
To eliminate duplicate values, use the DISTINCT clause.
The default separator between values in a group is comma (,). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''.
Example
In the following example, the result with values of column col1 in table t delimited by ':' is returned.
select GROUP_CONCAT(col1 separator ':') from (values('BBBB',1),('AAAA',1),('CCCC',2)) as t(col1 ,col2) group by col2;
group_concat
--------------
BBBB:AAAA
CCCC
(2 rows)
The following System Information functions are supported:
DATABASE
Description
Synonym for current_schema(). The concept of schema is similar to that of database in MySQL. You can specify the current schema by setting the search_path.
Syntax
DATABASE() returns TEXT
General rules
Returns the name of the schema that is first in the search path (or a null value if the search path is empty).
Like mysql,This is the schema that will be used for any tables or other named objects that are created without specifying a target schema.
Example
In the following example, current schema is lightdb.
select database();
database
----------
lighdb
(1 row)