Appropriately characterizing the fields in a table is critical to the general streamlining of your data set. You should utilize just the sort and size of field you truly need to utilize; don't characterize a field as 10 characters wide in the event that you realize you're simply going to utilize 2 characters. These sorts of fields (or segments) are additionally alluded to as information types, after the kind of information you will store in those fields.
MySQLi utilizes various information types broken into three classifications: numeric, date and time, and string types.
Numeric Data Types
MySQLi utilizes all the standard ANSI SQL numeric information types, so in case you're coming to MySQLi from an alternate data set framework, these definitions will look natural to you. The accompanying rundown shows the basic numeric information types and their depictions −
- INT − An ordinary estimated number that can be marked or unsigned. Whenever marked, the admissible reach is from - 2147483648 to 2147483647. In the event that unsigned, the reasonable reach is from 0 to 4294967295. You can determine a width of up to 11 digits.
- TINYINT − A minuscule whole number that can be marked or unsigned. Whenever marked, the suitable reach is from - 128 to 127. On the off chance that unsigned, the passable reach is from 0 to 255. You can indicate a width of up to 4 digits.
- SMALLINT − A little number that can be marked or unsigned. Whenever marked, the passable reach is from - 32768 to 32767. On the off chance that unsigned, the permissible reach is from 0 to 65535. You can indicate a width of up to 5 digits.
- MEDIUMINT − A medium-sized number that can be marked or unsigned. Whenever marked, the passable reach is from - 8388608 to 8388607. On the off chance that unsigned, the permissible reach is from 0 to 16777215. You can determine a width of up to 9 digits.
- BIGINT − A huge number that can be marked or unsigned. Whenever marked, the reasonable reach is from - 9223372036854775808 to 9223372036854775807. On the off chance that unsigned, the reasonable reach is from 0 to 18446744073709551615. You can determine a width of up to 20 digits.
- FLOAT(M,D) − A gliding point number that can't be unsigned. You can characterize the presentation length (M) and the quantity of decimals (D). This isn't needed and will default to 10,2, where 2 is the quantity of decimals and 10 is the complete number of digits (counting decimals). Decimal accuracy can go to 24 spots for a FLOAT.
- DOUBLE(M,D) − A twofold exactness coasting point number that can't be unsigned. You can characterize the showcase length (M) and the quantity of decimals (D). This isn't needed and will default to 16,4, where 4 is the quantity of decimals. Decimal exactness can go to 53 spots for a DOUBLE. Genuine is an equivalent for DOUBLE.
- DECIMAL(M,D) − An unloaded drifting point number that can't be unsigned. In unloaded decimals, every decimal relates to one byte. Characterizing the showcase length (M) and the quantity of decimals (D) is required. NUMERIC is an equivalent for DECIMAL.
Date and Time Types
The MySQL date and time datatypes are −
- DATE − A date in YYYY-MM-DD design, between 1000-01-01 and 9999-12-31. For instance, December 30th, 1973 would be put away as 1973-12-30.
- DATETIME − A date and time blend in YYYY-MM-DD HH:MM:SS design, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For instance, 3:30 in the early evening on December 30th, 1973 would be put away as 1973-12-30 15:30:00.
- TIMESTAMP − A timestamp between 12 PM, January 1, 1970 and at some point in 2037. This resembles the past DATETIME design, just without the hyphens between numbers; 3:30 in the early evening on December 30th, 1973 would be put away as 19731230153000 ( YYYYMMDDHHMMSS ).
- TIME − Stores the time in HH:MM:SS design.
- YEAR(M) − Stores a year in 2-digit or 4-digit design. On the off chance that the length is indicated as 2 (for instance YEAR(2)), YEAR can be 1970 to 2069 (70 to 69). In the event that the length is indicated as 4, YEAR can be 1901 to 2155. The default length is 4.
String Types
Albeit numeric and date types are fun, most information you'll store will be in string design. This rundown depicts the basic string datatypes in MySQLi.
- CHAR(M) − A fixed-length string somewhere in the range of 1 and 255 characters long (for instance CHAR(5)), right-cushioned with spaces to the predefined length when put away. Characterizing a length isn't needed, however the default is 1.
- VARCHAR(M) − A variable-length string somewhere in the range of 1 and 255 characters long; for instance VARCHAR(25). You should characterize a length while making a VARCHAR field.
- Mass or TEXT − A field with a most extreme length of 65535 characters. Masses are "Parallel Large Objects" and are utilized to store a lot of twofold information, for example, pictures or different kinds of documents. Fields characterized as TEXT likewise hold a lot of information; the contrast between the two is that sorts and examinations on put away information are case delicate on BLOBs and are not case touchy in TEXT fields. You don't indicate a length with BLOB or TEXT.
- TINYBLOB or TINYTEXT − A BLOB or TEXT segment with a greatest length of 255 characters. You don't indicate a length with TINYBLOB or TINYTEXT.
- MEDIUMBLOB or MEDIUMTEXT − A BLOB or TEXT segment with a most extreme length of 16777215 characters. You don't determine a length with MEDIUMBLOB or MEDIUMTEXT.
- LONGBLOB or LONGTEXT − A BLOB or TEXT section with a most extreme length of 4294967295 characters. You don't determine a length with LONGBLOB or LONGTEXT.
- ENUM − A count, which is an extravagant term for list. When characterizing an ENUM, you are making a rundown of things from which the worth should be chosen (or it tends to be NULL). For instance, in the event that you needed your field to contain "A" or "B" or "C", you would characterize your ENUM as ENUM ('A', 'B', 'C') and just those qualities (or NULL) might populate that field.