11.9 Using Data Types from Other Database Engines

To facilitate the use of code written for SQL implementations from other vendors, MySQL maps data types as shown in the following table. These mappings make it easier to import table definitions from other database systems into MySQL.

Other Vendor Type MySQL Type

note 'BOOL': integer-types.note 'TINYINT': integer-types.

note 'BOOLEAN': integer-types.note 'TINYINT': integer-types.

'CHARACTER VARYING(M)' 'VARCHAR(M)'

note 'FIXED': fixed-point-types.note 'DECIMAL': fixed-point-types.

note 'FLOAT4': floating-point-types.note 'FLOAT': floating-point-types.

note 'FLOAT8': floating-point-types.note 'DOUBLE': floating-point-types.

note 'INT1': integer-types.note 'TINYINT': integer-types.

note 'INT2': integer-types.note 'SMALLINT': integer-types.

note 'INT3': integer-types.note 'MEDIUMINT': integer-types.

note 'INT4': integer-types.note 'INT': integer-types.

'INT8' *note 'BIGINT': integer-types.

'LONG VARBINARY' *note 'MEDIUMBLOB': blob.

'LONG VARCHAR' *note 'MEDIUMTEXT': blob.

'LONG' *note 'MEDIUMTEXT': blob.

note 'MIDDLEINT': integer-types.note 'MEDIUMINT': integer-types.

note 'NUMERIC': fixed-point-types.note 'DECIMAL': fixed-point-types.

Data type mapping occurs at table creation time, after which the original type specifications are discarded. If you create a table with types used by other vendors and then issue a 'DESCRIBE TBL_NAME' statement, MySQL reports the table structure using the equivalent MySQL types. For example:

 mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG VARCHAR, d NUMERIC);
 Query OK, 0 rows affected (0.00 sec)

 mysql> DESCRIBE t;
 +-------+---------------+------+-----+---------+-------+
 | Field | Type          | Null | Key | Default | Extra |
 +-------+---------------+------+-----+---------+-------+
 | a     | tinyint(1)    | YES  |     | NULL    |       |
 | b     | double        | YES  |     | NULL    |       |
 | c     | mediumtext    | YES  |     | NULL    |       |
 | d     | decimal(10,0) | YES  |     | NULL    |       |
 +-------+---------------+------+-----+---------+-------+
 4 rows in set (0.01 sec)

 File: manual.info.tmp, Node: functions, Next: sql-statements, Prev: data-types, Up: Top

12 Functions and Operators **************************

• Menu:

• built-in-function-reference:: Built-In Function and Operator Reference
• loadable-function-reference:: Loadable Function Reference
• type-conversion:: Type Conversion in Expression Evaluation
• non-typed-operators:: Operators
• flow-control-functions:: Flow Control Functions
• numeric-functions:: Numeric Functions and Operators
• date-and-time-functions:: Date and Time Functions
• string-functions:: String Functions and Operators
• fulltext-search:: Full-Text Search Functions
• cast-functions:: Cast Functions and Operators
• xml-functions:: XML Functions
• bit-functions:: Bit Functions and Operators
• encryption-functions:: Encryption and Compression Functions
• locking-functions:: Locking Functions
• information-functions:: Information Functions
• spatial-analysis-functions:: Spatial Analysis Functions
• json-functions:: JSON Functions
• gtid-functions:: Functions Used with Global Transaction Identifiers (GTIDs)
• aggregate-functions-and-modifiers:: Aggregate Functions
• miscellaneous-functions:: Miscellaneous Functions

• precision-math:: Precision Math

Expressions can be used at several points in SQL statements, such as in the 'ORDER BY' or 'HAVING' clauses of note 'SELECT': select. statements, in the 'WHERE' clause of a note 'SELECT': select, note 'DELETE': delete, or note 'UPDATE': update. statement, or in *note 'SET': set-variable. statements. Expressions can be written using values from several sources, such as literal values, column values, 'NULL', variables, built-in functions and operators, loadable functions, and stored functions (a type of stored object).

This chapter describes the built-in functions and operators that are permitted for writing expressions in MySQL. For information about loadable functions and stored functions, see note server-loadable-functions::, and note stored-routines::. For the rules describing how the server interprets references to different kinds of functions, see *note function-resolution::.

An expression that contains 'NULL' always produces a 'NULL' value unless otherwise indicated in the documentation for a particular function or operator.

Note:

By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

To tell the MySQL server to accept spaces after function names by starting it with the '--sql-mode=IGNORE_SPACE' option. (See *note sql-mode::.) Individual client programs can request this behavior by using the 'CLIENT_IGNORE_SPACE' option for 'mysql_real_connect()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-connect.html). In either case, all function names become reserved words.

For the sake of brevity, some examples in this chapter display the output from the *note 'mysql': mysql. program in abbreviated form. Rather than showing examples in this format:

 mysql> SELECT MOD(29,9);
 +-----------+
 | mod(29,9) |
 +-----------+
 |         2 |
 +-----------+
 1 rows in set (0.00 sec)

This format is used instead:

 mysql> SELECT MOD(29,9);
         -> 2

 File: manual.info.tmp, Node: built-in-function-reference, Next: loadable-function-reference, Prev: functions, Up: functions