When creating a table is necessary to inform the columns that will be part of it as well as the type of data that will be stored. Each column will be assigned a name and what kind of data you will store and for each type of information is required to indicate a compatible data type. So let us know in the string data types that MySQL and so help you choose the best option when creating your tables and model your bank.
In MySQL there are three major groups of data they are numeric, date and text.
Numeric Data Type
This data type is used when you need to store numbers and they integer or decimal.
- TinyInt: Integer with or without sign. Sign with a valid range of values is from -128 to 127. No sign, the range of values is 0 to 255;
- Bit ou Bool: Integer that can be 0 or 1, widely used when need to perform validations and conditional (true and false);
- SmallInt: Integer with or without sign. Sign with a valid range of values is from -32768 to 32767. No sign, the range of values is 0 to 65535.;
- MediumInt: Integer with or without sign. Sign with a valid range of values is from -8,388,608 to 8,388,607. No sign, the value range is 0 to 16777215.;
- Integer, Int: Integer with or without sign. Sign with a valid range of values is from -2147483648 to 2147483647. No sign, the range of values is 0 to 429 496 295;
- BigInt: Integer with or without sign. Sign with a valid range of values is from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. No sign, the range of values is 0 to 18,446,744,073,709,551,615;
- Float: Small number in single-precision floating point. Valid values range from +38 to-3.402823466E-1.175494351E-38, 0 eté since 175494351E-38 to 3.402823466E +38;
- xReal, Double: Floating point number in double precision. Allowed values range from-1.7976931348623157E +308 to-2.2250738585072014E-308, 0 and 2.2250738585072014E from 1.7976931348623157E-308 to +308;
- Decimal, Dec, Numeric: Floating point number unpacked.
Date and Time Type
This data store information related to date and thus facilitate processing such as specific information.
- Date: Data type stores a date. The range of values goes from January 1, 1001 through December 31, 9999. The storage format is year-month-day;
- DateTime: Combination of date and time. The range of values goes from 1 January 1001 ed at 0 hours, 0 minutes and 0 seconds of 31 December 9999 to 23 hours, 59 minutes and 59 seconds. The storage format is year-month-day hours: minutes: seconds;
- TimeStamp: Combination of date and time. The margin ranges from January 1, 1970 to the year 2037;
- Time: Stores an hour. The margin of hours ranges from -838 hours, 59 minutes and 59 seconds. The storage format is ‘HH:MM:SS’;
- Year: Stores a year. The range of values allowed is from the year 1901 to the year 2155. The field can have two size 4 or size depending on whether we want to store the year with two or four digits;
This data type is used to store any type of information in the database.
- Char(n): Stores a fixed length text. It may contain from 0 to 255 characters;
- VarChar(n): Stores a text of variable length. The text may contain from 0 to 255 characters;
- TinyText e TinyBlob: Column with a maximum length of 255 characters;
- Blob e Text: Text with a maximum of 65535 characters;
- MediumBlob e MediumText: Text with a maximum of 16,777,215 characters.
- LongBlob e LongText: Text with a maximum of 4,294,967,295 characters;
- Enum: Field that can have a single value from a list that specifies. The Enum type supports up to 65535 different values.
- Set: Field that can contain none, one or more values from a list. The list can have a maximum of 64 values.
Any type of information can be considered as text in the database, but for some specific commands and processes is necessary to describe exactly what kind as it will be worked. For example, a number can be stored as text, but when you need to use it in some kind of calculation will need to turn it into an integer or decimal, this is reflected directly in the processing of information and therefore model the database optimally is essential.
Learn more about the advantages of using MySQL in PHP projects on our official site by clicking the link beside.