Download to read offline
mysql datatype______________________.docx
- 1. MYSQL DATATYPES The datatype of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on. Each column in a database table is required to have a name and a data type. MySQL data types: string, numeric, and date and time, binary, special types Data type Description Numeric Types INT(size) A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) BIGINT(size) A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255) FLOAT(m,d) A single-precision floating point number.It is a floating-point number that cannot be unsigned. You can define the display length (m) and the number of decimals (d). This is not required and will default to 10,2, where 2 is the number of decimals, and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a float type. It requires 2 bytes for storage. DOUBLE(m,d) It is a double-precision floating- point number that cannot be unsigned. You can define the display length (m) and the number of decimals (d). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a double. Real is a synonym for double. It requires 8 bytes for storage. DECIMAL(m,d) An unpacked floating-point number that cannot be unsigned.
- 2. (“unpacked" means thenumber is stored as a string, using one character for each digit of the value) In unpacked decimals, each decimal corresponds to one byte. Defining the display length (m) and the number of decimals (d) is required. Numeric is a synonym for decimal. String Types CHAR(size) Here size is the number of characters to store. Fixed-length strings. VARCHAR(size) Here size is the number of characters to store. Variable- length string. TEXT(size) Maximum size of 65,535 characters. Here size is the number of characters to store. Binary Types BINARY(size) Here size is the number of binary characters to store. Fixed-length strings. VARBINARY(size) Here size is the number of characters to store. Variable- length string. BLOB(size) Binary Large Object Data Types for storing binary data. It can hold the maximum size of 65,535 bytes. Special Types ENUM(val1, val2, val3, ...) A string object that can have only one value, chosen from a list of possible values. SET(val1, val2, val3, ...) A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list BOOLEAN Zero is considered as false, nonzero values are considered as true. BOOLEAN Equal to BOOL Date and Time Types DATE A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31' TIME A time. Format: hh:mm:ss. The supported range is from '- 838:59:59' to '838:59:59' TIMESTAMP A timestamp. TIMESTAMP values are stored as the number of seconds Format: YYYY-MM-DD hh:mm:ss. The supported range is
- 3. from '1970-01-01 00:00:01'UTC to '2038-01-09 03:14:07' UTC. DATETIME A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Enumerated Type (2mark qstn) Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. Unlike other types, Enumerated Types need to be created using CREATE TYPE command. This type is used to store a static, ordered set of values. For example compass directions, i.e., NORTH, SOUTH, EAST, and WEST or days of the week as shown below – CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'); Enumerated, once created, can be used like any other types. Syntax:: column_name DATATYPE ex: Emp_id INT