MySQL Data Types in Tables

The following data types are used in the MySQL code:

• int: This data type stands for integer and is used for the id and Telephone columns. It is used to store whole numbers, both positive and negative.

• char: This data type is used for the Name and Lastname columns. It is used to store strings with a fixed length, which is specified in parentheses. In this case, both Name and Lastname can store up to 30 characters.

• PRI: This is a keyword used in the Key column for the id column, which indicates that it is a primary key.

• NULL: This keyword is used in the Null column to indicate whether a column can contain null values, which means it can have no value assigned to it.

• auto_increment: This is used in the Extra column for the id column and specifies that the value for this column should be automatically generated and incremented for each new row added to the table.

mysql> DESCRIBE friends;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int      | NO   | PRI | NULL    | auto_increment |
| Name      | char(30) | YES  |     | NULL    |                |
| Lastname  | char(30) | YES  |     | NULL    |                |
| Telephone | int      | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

 

Common Datatypes used in MySQL

Some common datatypes used in MySQL are:

• INT: used to store integers.
• VARCHAR: used to store variable-length strings of characters.
• CHAR: used to store fixed-length strings of characters.
• TEXT: used to store large amounts of text.
• DATE: used to store dates in the format YYYY-MM-DD.
• DATETIME: used to store dates and times in the format YYYY-MM-DD HH:MM:SS.
• FLOAT: used to store floating-point numbers.
• BOOLEAN: used to store boolean values (true or false).

 

Why we use parentheses while working with char and varchar

Parentheses are used with CHAR and VARCHAR data types to specify the maximum number of characters that can be stored in a column. For example, if you define a column as VARCHAR(50), it means that this column can store up to 50 characters.

The reason for using parentheses is to help optimize storage space.

By specifying the maximum length of the column, MySQL can allocate just enough storage space to hold the data, rather than using a fixed amount of space for each column. This can help reduce the amount of disk space required and improve performance.

However, it is important to note that using excessively large values for the maximum length of a column can negatively impact performance, as it requires more storage space and can result in slower queries. Therefore, it is recommended to use reasonable and realistic maximum lengths for columns based on the actual data being stored.

 

BLOB in MySQL

BLOB (Binary Large Object) is a data type in MySQL that can be used to store large binary data, such as images, audio files, or videos, in the database.

BLOB can store up to 65,535 bytes of data, or even more if the database is configured to support larger BLOBs. BLOB data type can be useful when you need to store binary data directly in the database instead of on the file system.

BLOB in MySQL, or links to files ?

It is generally advised to store the links to files in the database rather than storing the files themselves using the BLOB data type.

This is because storing large files directly in the database can cause performance issues, and it can be more difficult to manage the files.

Storing links to files allows the files to be stored on a separate file server or in cloud storage, which can improve performance and scalability. Additionally, storing links to files allows the files to be easily updated or replaced without modifying the database.

 

Recommended datatype in MySQL while working with Financial Data

When working with financial data, it is recommended to use the DECIMAL data type in MySQL.

This is because the DECIMAL data type is precise and allows for exact decimal calculations, making it suitable for storing monetary values.

ther numeric data types like FLOAT or DOUBLE may not be as precise and can result in rounding errors when used for financial calculations.