REQUEST COMPLIMENTARY SQLS*PLUS LICENCE
MySQL data types – When determining the columns of the table for them, you must specify the data type. Each column must have a data type. The data type determines what values can be stored in a column, how much memory space they will occupy.
MySQL provides the following data types, which can be divided into a number of groups.
In the following we will show a list of different data types that are available in MySQL, and include string, numeric, date/time, and types of large (large objects).
The String data types in MySQL are listed below:
| Syntax | Maximum size | Explanation |
|---|---|---|
| CHAR(size) | Size 255 characters | Where size is the number of saved characters. Strings with fixed length. The space to the right is extended by an equal number of characters. |
| VARCHAR(size) | Size 255 characters | Where size is the number of saved characters. String of variable length. |
| TINYTEXT(size) | Size 255 characters | Where size is the number of saved characters. |
| TEXT(size) | Size 65535 characters | Where size is the number of saved characters. |
| MEDIUMTEXT(size) | Size 16777215 characters | Where size is the number of saved characters. |
| LONGTEXT(size) | Size 4Gb or 4294967295 characters | Where size is the number of saved characters. |
| BINARY(size) | Size 255 characters | Where size is the number of binary characters to store. Strings with fixed lengths. The space on the right is expanded by an equal number of characters. (Represented in MySQL 4.1.2). |
| VARBINARY(size) | Size 255 characters | Where size is the number of saved characters. String of variable length. (Presented in MySQL 4.1.2). |
Starting with MySQL 5.6 types CHAR and VARCHAR use UTF-8 encoding by default, which allows to use up to 3 bytes for storing a character depending on the language (for many European languages 1 byte per character, for a number of Eastern European and Middle Eastern languages – 2 bytes, and for Chinese, Japanese, Korean – 3 bytes per character).
The numeric data types in MySQL are listed below:
| Syntax | Maximum size | Explanation |
|---|---|---|
| BIT | Very small integer value, equivalent to TINYINT (1). Numerical expression from -128 to 127. Positive values of numbers begin with 0 to 255. | |
| TINYINT(m) | It’s a very small integer value. Numerical expression from -128 to 127. Positive values of numbers begin with 0 to 255. | |
| SMALLINT(m) | Small integer value. Numerical expression from -32768 to 32767. Positive values of numbers begin with 0 to 65535. | |
| MEDIUMINT(m) | Average integer value. Numerical expression from -8388608 to 8388607. Positive values of numbers begin with 0 to 16777215. | |
| INT(m) | Standard integer value. Numerical expression from -2147483648 to 2147483647. Positive values of numbers begin with 0 to 4294967295. | |
| INTEGER(m) | Standard integer value. The signed values range from -2147483648 to 2147483647. Positive values of numbers begin with 0 to 4294967295. | This is synonymous with INT data type. |
| BIGINT(m) | A large integer value. The signed values range from -9223372036854775808 to 9223372036854775807. Positive values of numbers begin with 0 to 18446744073709551615. | |
| DECIMAL(m,d) | A number with a fixed point. m by default – 10, if not specified. d is 0 by default, if not specified. | Where m is all digits, and d is the number of digits after the decimal place. |
| DEC(m,d) | A number with a fixed point. m by default – 10, if not specified. d is 0 by default, if not specified. | Where m is all digits, and d is the number of digits after the decimal place. This is synonymous with the DECIMAL type. |
| NUMERIC(m,d) | A number with a fixed point. m by default – 10, if not specified. d is 0 by default, if not specified. | Where m is all digits, and d is the number of digits after the decimal place. This is synonymous with the DECIMAL type. |
| FIXED(m,d) | A number with a fixed point. m by default – 10, if not specified. d is 0 by default, if not specified. | Where m is all digits, and d is the number of digits after the decimal place. (Represented in MySQL 4.1) This is synonymous with the DECIMAL type. |
| FLOAT(m,d) | A floating point number with single precision. | Where m is all digits, and d is the number of digits after the decimal place. |
| DOUBLE(m,d) | A floating point number with double precision. | Where m is all digits, and d is the number of digits after the decimal place. This is synonymous with the DOUBLE data type. |
| DOUBLE PRECISION(m,d) | A floating point number with double precision. | Where m is all digits, and d is the number of digits after the decimal place. |
| REAL(m,d) | A floating point number with double precision. | Where m is all digits, and d is the number of digits after the decimal place. This is synonymous with the DOUBLE data type. |
| FLOAT(p) | A floating point number. | Where p is precision. |
| BOOL | TINYINT synonyms (1) | Considered as boolean data type, where value 0 is considered FALSE and any other value is considered TRUE. |
| BOOLEAN | TINYINT synonyms (1) | Considered as boolean data type, where value 0 is considered FALSE and any other value is considered TRUE. |
Below are the date / time data types in MySQL:
| Syntax | Maximum size | Conclusion value |
|---|---|---|
| DATE | The values range from ‘1000-01-01’ to ‘9999-12-31’. | ‘YYYY-MM-DD’. |
| DATETIME | The values range from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. | ‘YYYY-MM-DD HH:MM:SS’. |
| TIMESTAMP(m) | The values range from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. | ‘YYYY-MM-DD HH:MM:SS’. |
| TIME | The values range from ‘-838: 59: 59’ to ‘838: 59’. | ‘HH:MM:SS’. |
| YEAR[(2|4)] | The value of the year as 2 or 4 digits. | The default value is 4 digits. |
The data types of a large object (LOB) in MySQL are listed below:
| Syntax | Maximum size | Explanation |
|---|---|---|
| TINYBLOB | Maximum size 255 bytes. | |
| BLOB(size) | Maximum size 65 535 bytes. | Where size – number of stored characters (size is optional and was entered in MySQL 4.1) |
| MEDIUMBLOB | Maximum size 16,777,215 bytes. | |
| LONGTEXT | Maximum size 4 GB or 4 294 967 295 characters. |
When choosing the data type for a column, you should first define the general data class that is best used for the column: numeric, string or temporary;
Next, you need to select a specific data type from those presented in the class. Many MySQL data types allow you to store data of the same type, but with different range of values, accuracy or required physical space (on disk or in memory). Some types have special behavior or properties.
For example, you can store the same type of data in DATETIME and TIMESTAMP columns: date and time, to the nearest second. However, TIMESTAMP type requires half the space, allows to work with time zones and has special means of automatic updating. On the other hand, the range of acceptable values for it is much narrower.
What to pay attention to when choosing the data type:
PreambleNoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...
PreambleMongoDB Conditional operators specify a condition to which the value of the document field shall correspond.Comparison Query Operators $eq...
5 Database management trends impacting database administrationIn the realm of database management systems, moreover half (52%) of your competitors feel...
The data type is defined as the type of data that any column or variable can store in MS SQL Server. What is the data type? When you create any table or...
PreambleMS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a query.SQL Server accepts,...
First the basics: what is the master/slave?One database server (“master”) responds and can do anything. A lot of other database servers store copies of all...
PreambleAtom Hopper (based on Apache Abdera) for those who may not know is an open-source project sponsored by Rackspace. Today we will figure out how to...
PreambleMongoDB recently introduced its new aggregation structure. This structure provides a simpler solution for calculating aggregated values rather...
FlexibilityOne of the most advertised features of MongoDB is its flexibility. Flexibility, however, is a double-edged sword. More flexibility means more...
PreambleSQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.Why use it?If you...
PreambleWriting an application on top of the framework on top of the driver on top of the database is a bit like a game on the phone: you say “insert...
PreambleOracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...
PreambleIBM pureXML, a proprietary XML database built on a relational mechanism (designed for puns) that offers both relational ( SQL / XML ) and...
What is PostgreSQL array? In PostgreSQL we can define a column as an array of valid data types. The data type can be built-in, custom or enumerated....
PreambleIf you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.In this...
PreambleStarting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...