Numbers
Storing Whole Numbers:
If you’re storing whole numbers, use one of the integer types: TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT. These require 8, 16, 24, 32, and 64 bits of storage space, respectively. They can store values from -2(N–1) to 2(N–1)–1, where N is the number of bits of storage space they use. If you do not want the negative values to be stored, use the UNSIGNED attribute with the field. It will double the storage space of the datatype. For example, a TINYINT UNSIGNED can store values ranging from 0 to 255 instead of from -128 to 127.
Storing Real Numbers:
Real numbers are the numbers with fractional part. MySQL provide three datatypes to store Real numbers. These datatypes are:
DECIMAL: The DECIMAL type is for storing exact fractional numbers. A DECIMAL number in MySQL 5.0 and newer can have up to 65 digits. DECIMAL type allows us to specify the maximum didits before and after the decimal point. This increases the space consumption in DECIMAL type. So DECIMAL type should be used only when you need mathematical data of high precision and you don’t care about the storage space.
FLOAT: Note that “Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision”.
DOUBLE: DOUBLEs are just like floats, except for the fact that they are twice as large. This allows for a greater accuracy. Both FLOAT and DOUBLE represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers. Floating-point types typically use less space than DECIMAL to store the same range of values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
Strings
VARCHAR: VARCHAR is a variable length type. It requires less space than fixed length types (CHAR) because it uses only as much space as it needs. But it uses 1 or 2 extra bytes to store the length of the data. So VARCHAR(10) will use 11 bytes whereas VARCHAR(1000) will use 12 bytes of storage space.
Pros:
1. Less storage space.
2. Improved performance because of saved space.
Cons:
1. Updates are slow due to variable-length.
So you should use VARCHAR type when you do not know about the maximum length of the data in advance.
CHAR: CHAR is fixed-length and is useful if you want to store very short strings. When storing a CHAR value, MySQL removes any trailing spaces. It is good to use CHAR type if all the values are nearly the same length.
BLOB and TEXT: BLOB and TEXT are string data types designed to store large amounts of data as either binary or character strings, respectively. The only difference between the BLOB and TEXT families is that BLOB types store binary data with no collation or character set, but TEXT types have a character set and collation. These types should be used only if data to be stored is of very large length. Otehrwise avoid these types as it will make the queries slow and provide no indexing.
ENUM: ENUM store a predefined set of distinct string values. Each string is internally stored as an integer. In a simple select query, you will see the integer value as output instead of trhe string. ENUM should only be used if you are sure that no other values will be entered or removed from the list. Also there is a overhead of mapping the integer ID of ENUM values with the actual string representation which may degrade the performance in some cases.
Date and Time
DATETIME: DATETIME stores the date and time in YYYYMMDDHHMMSS format without considering the timezone. Is uses uses eight bytes of storage space.
TIMESTAMP: TIMESTAMP stores the date and time in the UNIX timestamp format i.e the number of seconds elapsed since midnight, January 1, 1970, Greenwich Mean Time (GMT). TIMESTAMP uses only four bytes of storage space. TIMESTAMP depends on the timezone. TIMESTAMP also provides the auto update property. So always try to use TIMESTAMP type than DATETIME.
Is the TEXT column type available in Oracle and in that case, can I export a MySQL table with such a column into a file, then import that file back into Oracle ?