Understanding MySQL Tables

Understanding MySQL Tables

You have a lot of flexibility when it comes to setting up MySQL tables. To have your MySQL database operate as efficiently as possible, you want to have the columns be assigned to the most appropriate size and data type to hold the data you need to store.

Use the following tables as a reference to the different data types that can be assigned to your columns. Data types available for use in MySQL fall into these categories: numbers, time/date, and character strings. Here are a few things you need to know as you read these tables:

  • The maximum display size for a column is 255 characters. An M data type option sets the number of characters that are displayed and, in most cases, stored for the column.

  • There can be up to 30 digits following the decimal point for floating-point data types. A D option to a data type indicates the number of digits allowed for a floating-point number following the decimal point. (The value should be no more than two digits less than the value of the display size being used.)

  • The UNSIGNED option (shown in braces) indicates that only positive number to be allowed in the column. This allows the column to hold larger positive numbers.

  • The ZEROFILL option (shown in braces) indicates that the data in the column will be padded with zeros. For example, the number 25 in a column with a data type of INTEGER(7) ZEROFILL would appear as 0000025. (Any ZEROFILL column automatically becomes UNSIGNED.)

  • All values shown in braces are optional.

  • The parentheses shown around the (M) and (D) values are necessary if you enter either of those values. In other words, don't type the braces, but do type the parentheses.

Table 24-2 shows numeric data types that you can use with MYSQL.

Table 24-2: Numeric Data Types for Columns

Data Type

Description

Space Needed

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

Can contain large integers with the following allowable values:

-9223372036854775808 to 9223372036854775807 (unsigned)

0 to 18446744073709551615 (signed)

Uses 8 bytes

DECIMAL[(M[,D])] [ZEROFILL]

Contains an unpacked floating-point number (signed only). Each digit is stored as a single character. When you choose the display value (M), decimal points and minus signs are not counted in that value. The value of (M) is 10 by default. Setting D to zero (which is the default) causes only whole numbers to be used.

Uses M+2 bytes if D is greater than 0

Uses M+1 bytes if D is equal to 0

DOUBLE[(M,D)] [ZEROFILL]

Contains a double-precision, floating-point number of an average size. Values that are allowed include:

-1.7976931348623157E+308 to -2.2250738585072014E-308

0

2.2250738585072014E-308 to 1.7976931348623157E+308.

Uses 8 bytes

DOUBLE PRECISION

Same as DOUBLE.

Same as DOUBLE.

FLOAT(X) [ZEROFILL]

Contains a floating-point number. For a single-precision floating-point number X can be less than or equal to 24. For a double-precision floating-point number, X can be between 25 and 53. The display size and number of decimals are undefined.

Uses 4 bytes

FLOAT[(M,D)] [ZEROFILL]

Contains a single-precision floating-point number. Values that are allowed include:

-3.402823466E+38 to -1.175494351E-38

0

1.175494351E-38 to 3.402823466E+38.

If the display value (M) is less than or equal to 24, the number is a single-precision floating-point number.

Uses 4 bytes if X is less than or equal to 24

Uses 8 bytes if X is greater than or equal to 25 and less than or equal to 53

INT[(M)] [UNSIGNED] [ZEROFILL]

Contains an integer of normal size. The range is -2147483648 to 2147483647 if it's signed and 0 to 4294967295 if unsigned.

Uses 4 bytes

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

Same as INT.

Same as INT

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

Contains an integer of medium size. The range is

-8388608 to 8388607 if it's signed and 0 to 16777215 if unsigned.

Uses 3 bytes

NUMERIC(M,D) [ZEROFILL]

Same as DECIMAL.

Same as DECIMAL

REAL

Same as DOUBLE.

Same as DOUBLE.

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

Contains an integer of small size. The range is

-32768 to 32767 if it's signed and 0 to 65535 if it's unsigned.

Uses 2 bytes

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer, with a signed range of -128 to 127 and a 0 to 255 unsigned range.

Uses 1 byte

The default format of dates in MySQL is YYYY-MM-DD, which stands for the year, month, and day. Any improperly formatted date or time values will be converted to zeros. Table 24-3 shows time and date data types that you can use with MYSQL.

Table 24-3: Time/Date Data Types for Columns

Data Type

Description

Space Needed

DATE

Contains a date between the range of January 1, 1000 (1000-01-01) and December 31, 9999 (9999-12-31).

Uses 3 bytes

DATETIME

Contains a combination of date and time between zero hour of January 1, 1000 (1000-01-01 00:00:00) and the last second of December 31, 9999 (9999-12-31 23:59:59).

Uses 8 bytes

TIMESTAMP[(M)]

Contains a timestamp from between zero hour of January 1, 1970 (1970-01-01 00:00:00) and a time in the year 2037. It is stored in the form: YYYYMMDDHHMMSS. Using (M), you can reduce the size of the TIMESTAMP displayed to less than the full 14 characters (though the full 4-byte TIMESTAMP is still stored).

Uses 4 bytes

TIME

Contains a time between -838:59:59 and 838:59:59. The format of the field is in hours, minutes, and seconds (HH:MM:SS).

Uses 3 bytes

YEAR[(2|4)]

Contains a year, represented by either two or four digits. For a four-digit year, YEAR mean 1901–2155 (0000 is also allowed). For a two-digit year, the digits 70-69 can .represent 1970-2069

Uses 1 byte

Table 24-4 shows string data types that you can use with MYSQL.

Table 24-4: String Data Types for Columns

Data Type

Description

Space Needed

BLOB

Contains a binary large object (BLOB) that varies in size, based on the actual value of the data, rather than on the maximum allowable size. Searches on a BLOB column are case-sensitive.

Uses up to L+2 bytes, where L is less than or equal to 65535

[NATIONAL] CHAR(M) [BINARY]

Contains a character string of fixed length, with spaces padded to the right to meet the length. To display the value, the spaces are deleted. The value of (M) determines the number of characters (from 1 to 255). If the BINARY keyword is used, sorting of values is case-sensitive (it is case-insensitive by default). The NATIONAL keyword indicates that the default CHARACTER set should be used.

Uses between 1 and 255 bytes, based on the value of (M)

ENUM('val1','val2',...)

Contains enumerated strings that are typically chosen from a list of values indicated when you create the column. For example, you set a column definition to ENUM("dog","cat","mouse"). Then, if you set the value of that column to "1" the value displayed would be "dog", "2" would be "cat" and "3" would be mouse. It lets you take a number as input and have a string as output. Up to 65535 values are allowed.

Uses either 1 byte (for up to about 255 values) or 2 bytes, (for up to 65535 values)

LONGBLOB

Contains a binary large object (BLOB) that varies in size, based on the actual value of the data, rather than on the maximum allowable size. LONGBLOB allows larger values than MEDIUMBLOB. Searches on a LONGBLOB column are case-sensitive.

Uses up to L+4 bytes, where L is less than or equal to 4294967295

LONGTEXT

Same as LONGBLOB, except that searching is done on these columns in case-insensitive style.

Uses up to L+4 bytes, where L is less than or equal to 4294967295

MEDIUMBLOB

Contains a binary large object (BLOB) that varies in size, based on the actual value of the data, rather than on the maximum allowable size. MEDIUMBLOB allows larger values than BLOB. Searches on a MEDIUMBLOB column are case-sensitive.

Uses up to L+3 bytes, where L is less than or equal to 16777215

MEDIUMTEXT

Same as MEDIUMBLOB, except that searching is done on these columns in case-insensitive style.

Uses up to L+3 bytes, where L is less than or equal to 16777215

SET('val1','val2',...)

Contains a set of values. A SET column can display zero or more values from the list of values contained in the SET column definition. Up to 64 members are allowed.

Uses 1, 2, 3, 4 or 8 bytes, varying based on how many of the up to 64 set members are used.

TEXT

Same as BLOB, except that searching is done on these columns in case-insensitive style.

Uses up to L+2 bytes, where L is less than or equal to 65535

TINYBLOB

Contains a binary large object (BLOB) that varies in size, based on the actual value of the data, rather than on the maximum allowable size. TINYBLOB allows smaller values than BLOB. Searches on a TINYBLOB column are case-sensitive.

Uses up to L+1 bytes, where L is less than or equal to 255

TINYTEXT

Same as TINYBLOB, except that searching is done on these columns in case-insensitive style.

Uses up to L+1 bytes, where L is less than or equal to 255

[NATIONAL] VARCHAR(M) [BINARY]

Contains a character string of variable length, with no padded spaces added. The value of (M) determines the number of characters (from 1 to 255). If the BINARY keyword is used, sorting of values is case-sensitive (it is case-insensitive by default). The NATIONAL keyword indicates that the default CHARACTER set should be used.

Uses L+1 bytes, where L is less than or equal to M and M is from 1 to 255 characters




Part IV: Red Hat Linux Network and Server Setup
 
ASPTreeView.com
 
Evaluation has ДОЗЗЕБГЅµјЪexpired.
Info...