MySQL Server and Data Types

MySQL separates server initialization, schema definition, and connection configuration. A production installation should initialize an empty data directory once, store configuration outside that directory, and manage the server through the platform service manager. mysqld --initialize creates the data dictionary and an initial administrative account; it does not configure an application schema or privileges.

Accounts, Grants, and Roles #

MySQL identifies an account by both user and host, so 'app'@'localhost' and 'app'@'10.%' are distinct identities. Create the account with an explicit authentication policy:

sql

CREATE USER IF NOT EXISTS 'app'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'generated-secret';

In MySQL 8, CREATE USER creates the identity and GRANT assigns privileges; GRANT does not create the account. Host matching happens before authentication, after which roles and direct grants determine authorization. Use SHOW GRANTS to inspect effective privileges. Roles group privileges for reuse across accounts:

sql

CREATE ROLE 'order_reader';
GRANT SELECT ON app.orders TO 'order_reader';
GRANT 'order_reader' TO 'app'@'localhost';
SET DEFAULT ROLE 'order_reader' TO 'app'@'localhost';

Keep account administration outside application migration tables. Application schemas should not depend on ad hoc privilege changes to remain valid.

Numeric Types #

Choose an integer type from its range, not its display width. TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT use 1, 2, 3, 4, and 8 bytes. Unsigned types shift the range to non-negative values but introduce cross-database portability concerns. BOOLEAN and BOOL are aliases for TINYINT(1).

Use DECIMAL(p, s) for quantities that require decimal arithmetic, such as currency. p is total decimal digits and s is digits after the decimal point. FLOAT and DOUBLE represent binary floating-point values and can round decimal fractions; do not use them for equality-sensitive monetary values.

Temporal and Structured Values #

DATE stores a calendar date; TIME stores a time or duration.
DATETIME stores a literal date and time without timezone conversion.
TIMESTAMP is stored as an instant and converted between the session time zone and UTC for supported values.
JSON validates JSON and provides JSON functions, but its values are not a substitute for relational columns used in joins and indexes.

Use a DATETIME plus an explicit business timezone when the value is a local appointment. Use a UTC TIMESTAMP or UTC DATETIME for an event instant, and make the application conversion boundary explicit.

Temporal columns can declare fractional-second precision from 0 through 6, such as DATETIME(6). Strict SQL modes should reject invalid dates; modes controlling zero dates are version-sensitive, so schemas should not use '0000-00-00' as a missing-value sentinel. Use NULL or an explicit status instead.

Text, Binary, JSON, and Spatial Values #

CHAR and VARCHAR store character strings interpreted through a character set and collation. BINARY and VARBINARY store byte strings, while TEXT and BLOB families provide larger character and binary values. Index width is measured in bytes, so an utf8mb4 key can consume more index space than its character count suggests.

MySQL stores JSON in a binary format and exposes path, extraction, mutation, and validation functions. Index frequently queried scalar paths through generated columns or supported functional indexes. Spatial types such as POINT, LINESTRING, and POLYGON carry geometry; declare and validate the spatial reference system when geographic meaning matters.

Schema Inspection and Limits #

Use SHOW CREATE TABLE to inspect a table’s effective DDL and INFORMATION_SCHEMA for portable metadata queries:

sql

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'orders'
ORDER BY ordinal_position;

Storage limits depend on the storage engine, page size, row format, character set, and MySQL release. SHOW ENGINES, SHOW TABLE STATUS, and SHOW CREATE TABLE reveal effective engine and table properties. Verify row and index limits for the deployed version rather than encoding fixed assumptions into a schema generator. Related design details are covered in MySQL String Types and Character Sets, MySQL Indexes, and MySQL Tables and Views.