Data types in MySQL

1. Practical Experience Section

Oracle--->mysql

Number--->int (12)

Number (12,2)--->decimal (12,2) or float (12,2) or double (12,2)

VARCHAR2--->varchar

CLOB--->blob DATE--->date (or datetime)

Oracle: Long can store 2G size characters, CLOB can store 4G size characters (CLOB:4GB character data)

MySQL: BLOB, binary string variable; 2 + actual string length, storing binary data such as: Picture, attachment, binary document--------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------

2. Baidu part: char (m): The char data type is used to represent a string of length M. varchar (M): varchar can hold variable-length strings, and the maximum length supported by the varchar data type is 255 characters. The INT (M) [Unsigned]:int data type is used to hold any integer data from the range 2147483647 to 2147483648. If the user uses the unsigned option, the valid data range is adjusted to 0-4294967295. The float [(m,d)]:float data type is used to represent floating point data with smaller values, which provides more accurate data accuracy. where m represents the length of the floating-point data (that is, the sum of the data length around the decimal point), and D represents the number of numeric digits to the right of the decimal point. The Date:date data type is used to hold date data, and the default format is YYYY-MM-DD. The Text/blob:text and BLOB data types can be used to hold 255 to 65,535 characters, text is case insensitive, and blobs are case sensitive to characters. The Set:set data type is a combination of multiple data values, and any part or all of the data values are valid data for that data type. The Set data type can contain a maximum of 64 specified data values. The Enum:enum data type and set are basically the same, except that the enum allows only one valid data value to be selected. Text Type: Data type description CHAR (size) holds a fixed-length string (which can contain letters, numbers, and special characters). Specifies the length of the string in parentheses. A maximum of 255 characters. VARCHAR (size) holds variable-length strings (which can contain letters, numbers, and special characters). Specifies the maximum length of the string in parentheses. A maximum of 255 characters. Note: If the value is longer than 255, it is converted to the text type. Tinytext holds a string with a maximum length of 255 characters. TEXT holds a string with a maximum length of 65,535 characters.

Tinytext holds a string with a maximum length of 255 characters. TEXT holds a string with a maximum length of 65,535 characters. BLOBs are used for BLOBs (Binary Large OBjects). Store up to 65,535 bytes of data. Mediumtext holds a string with a maximum length of 16,777,215 characters. Mediumblob for BLOBs (Binary Large OBjects). Store up to 16,777,215 bytes of data. Longtext holds a string with a maximum length of 4,294,967,295 characters. Longblob for BLOBs (Binary Large OBjects). Store up to 4,294,967,295 bytes of data. ENUM (x,y,z,etc.) allows you to enter a list of possible values. The maximum of 65,535 values can be listed in the enum list. If the inserted value does not exist in the list, a null value is inserted. Note: These values are stored in the order that you entered them. Possible values can be entered in this format: enum (' X ', ' Y ', ' Z ') set is similar to enum, set can contain up to 64 list items, but set can store more than one value. Number Type: (* These integer types have additional option unsigned. Generally, integers can be negative or positive. If you add the unsigned property, the range will start at 0 instead of a negative number. Data type description TINYINT (size)-128 to 127 general. 0 to 255 unsigned *. Specify the maximum number of digits in parentheses. SMALLINT (size)-32768 to 32767 general. 0 to 65535 unsigned *. Specify the maximum number of digits in parentheses. Mediumint (size)-8388608 to 8388607 normal. 0 to 16777215 unsigned *. Specify the maximum number of digits in parentheses. INT (size)-2147483648 to 2147483647 general. 0 to 4294967295 unsigned *. Specify the maximum number of digits in parentheses. BIGINT (size)-9223372036854775808 to 9223372036854775807 general. 0 to 18446744073709551615 unsigned *. Specify the maximum number of digits in parentheses. Float (size,d) a small number with a floating decimal point. Specify the maximum number of digits in parentheses. Specifies the maximum number of digits to the right of the decimal point in the D parameter. Double (size,d) a large number with a floating decimal point。 Specify the maximum number of digits in parentheses. Specifies the maximum number of digits to the right of the decimal point in the D parameter. Decimal (SIZE,D) is a double type stored as a string, allowing a fixed decimal point. Date Type: (* even if DATETIME and TIMESTAMP return the same format, they work in a different way.) In an INSERT or UPDATE query, TIMESTAMP automatically sets itself to the current date and time. TIMESTAMP also accepts different formats, such as YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD. The data type describes date () dates. Format: YYYY-MM-DD. Note: The supported range is a combination of date and time from ' 1000-01-01 ' to ' 9999-12-31 ' datetime () *. Format: Yyyy-mm-dd HH:MM:SS. Note: The supported range is from ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 ' TIMESTAMP () * timestamp. The timestamp value is stored using the description of the Unix era (' 1970-01-01 00:00:00 ' UTC) to date. Format: Yyyy-mm-dd HH:MM:SS. Note: The supported range is from ' 1970-01-01 00:00:01 ' UTC to ' 2038-01-09 03:14:07 ' UTC time (). Format: HH:MM:SS. Note: The supported range is from ' -838:59:59 ' to ' 838:59:59 ' year () in 2-bit or 4-bit format. Note: The 4-bit format allows values from 1901 to 2155. The 2-bit format allows values from 70 to 69, representing 1970 to 2069.

Data types in MySQL