Common data types in SQL databases mainly include four categories: 1. Number types, such as INT, BIGINT, SMALLINT, used to store integers, DECIMAL is used for precise decimals, FLOAT and REAL is used for approximation; 2. String types, such as CHAR and VARCHAR are used for fixed-length and variable-length text, TEXT series is used for large text storage, NCHAR and NVARCHAR supports Unicode; 3. Date and time types, including DATE, TIME, DATETIME, TIMESTAMP and YEAR, among which TIMESTAMP is more suitable for processing time zones; 4. Other types such as BOOLEAN are used for boolean values, BLOB is used for binary objects, UUID and JSON are used for unique identification and structured data storage. Regular selection of data types can help improve performance, save storage and ensure data integrity.

When working with SQL databases, understanding common data types is key to designing efficient tables and managing data correctly. Each column in a table must have a defined data type, which determines what kind of values it can store. Here are the most widely used SQL data types and what they're typically used for.

1. Numeric Data Types – For Numbers, Big and Small

SQL provides several data types for storing numbers, and choosing the right one depends on whether you need integers or decimals, and how large the values might be.

• INT or INTEGER : Used for whole numbers. Usually take up 4 bytes and can store values from around -2 billion to 2 billion.
• BIGINT : When you need larger numbers than INT , this is your go-to. It uses 8 bytes and can handle numbers up to roughly 9 quintillion.
• SMALLINT : If you're sure the numbers will stay small (like a rating from 1 to 10), this saves space—uses only 2 bytes.
• DECIMAL(p, s) or NUMERIC(p, s) : For precision decimal numbers, like money. p is precision (total digits), and s is scale (digits after the decimal).
• FLOAT and REAL : For approximately numeric values. These are useful for scientific calculations but not recommended for exact values like currency.

A common mistake is using FLOAT for financial data—this can lead to rounding errors. Instead, DECIMAL is the safer choice.

2. String Data Types – Storing Text of Various Lengths

Text data is handled with string types, and SQL has different options depending on whether the length is fixed or variable.

• CHAR(n) : Fixed-length strings. If you define it as CHAR(10) and store "cat", it still uses 10 characters of space, padded with spaces.
• VARCHAR(n) or TEXT : Variable-length strings. More space-efficient than CHAR , especially when text length varies.
• TEXT , TINYTEXT , MEDIUMTEXT , LONGTEXT (MySQL specific): Used for large amounts of text without specifying a length.
• NCHAR and NVARCHAR : Same as CHAR and VARCHAR but for Unicode characters—useful for multilingual content.

For example, if you're storing user names, VARCHAR(50) is usually a good fit. If you're storing long articles, go with TEXT .

3. Date and Time Data Types – Tracking When Things Happen

Handling dates and times properly is cruel in applications like booking systems or logs. SQL has several types for this:

• DATE : Stores just the date (YYYY-MM-DD).
• TIME : Stores time of day (HH:MM:SS).
• DATETIME or TIMESTAMP : Stores both date and time. The difference between them varies by database— DATETIME often has a larger range, while TIMESTAMP might auto-update or convert time zones.
• YEAR : For storing just the year (sometimes used in reports or historical data).

One thing to watch out for: time zones. If your app is global, consider using TIMESTAMP instead of DATETIME since it often handles time zone conversion better.

4. Other Commonly Used Types – Booleans, Binary, and More

Not all data fits neatly into numbers or text. Some data types handle special cases:

• BOOLEAN or BOOL : Stores true/false values. Internally, it's often stored as 1 or 0.
• BLOB : Binary Large Object. Used for storing binary data like images, audio files, or serialized objects.
• UUID or CHAR(36) : For unique identifiers, especially in distributed systems.
• JSON : Some databases like MySQL and PostgreSQL support native JSON types for storing structured data inside a column.

Using BLOB to store images directly in the database is possible, but it can slow things down. A common alternative is to store the file path or URL in the database and keep the actual file on disk or in cloud storage.

That's a quick tour of the most common SQL data types. Choosing the right type for each column helps with performance, storage, and data integrity. It's not overly complicated, but it does require thinking ahead about what kind of data you'll handle.

The above is the detailed content of What are common SQL data types?. For more information, please follow other related articles on the PHP Chinese website!