Ispirer Website
Ispirer Capabilities - MySQL Migration
Free Trial
MySQL Data Types - DECIMAL and NUMERIC
Versions: MySQL 5.x, 4.x and 3.23
| MySQL - DECIMAL | |
|---|---|
| Syntax | DECIMAL[(p[,s])] [UNSIGNED] [ZEROFILL] |
| Data | Fixed-point numeric data |
| Parameters | p is the maximum number of all digits (both sides of the decimal point), s is the maximum number of digits after the decimal point If UNSIGNED is specified, negative values are not allowed. ZEROFILL specifies that the data is right padded with zeros to p |
| Range | 1 ⇐ p ⇐ 65, 0 ⇐ s ⇐ 30 |
| Default | p is 10, s is 0 |
| Internals | DECIMAL values are stored using a packed binary format that requires 4 bits to store 1 decimal digit |
| Storage Size | p/2 bytes |
| Version Differences | MySQL 5.0.3 and earlier - DECIMAL values are stored as character strings (one byte per one decimal digit) MySQL 4.1 - FIXED synonym added MySQL 4.0 - DEC synonym added MySQL 3.23 and earlier - p includes the space for the sign and the decimal point |
| Synonyms | DEC, FIXED |
| Standards | ANSI SQL |
NUMERIC is identical to DECIMAL
| MySQL - NUMERIC | |
|---|---|
| Standards | ANSI SQL |
MySQL DECIMAL and NUMERIC - Equivalents in Other Databases
| Database | Data Type and Conversion |
|---|---|
| Oracle | NUMBER(p,s), 1 ⇐ p ⇐ 38, -84 ⇐ s ⇐ 127 |
| SQL Server | DECIMAL(p,s), NUMERIC(p,s), 1 ⇐ p ⇐ 38, 0 ⇐ s ⇐ p |
| PostgreSQL | DECIMAL(p,s), NUMERIC(p,s), 1 ⇐ p ⇐ 1000, 0 ⇐ s ⇐ p |
| Sybase ASE | DECIMAL(p,s), NUMERIC(p,s), 1 ⇐ p ⇐ 38, 0 ⇐ s ⇐ p |
| Informix | DECIMAL(p,s), NUMERIC(p,s), 1 ⇐ p ⇐ 32, 0 ⇐ s ⇐ p |
| HP Neoview | DECIMAL(p,s), 1 ⇐ p ⇐ 18, 0 ⇐ s ⇐ p, NUMERIC(p,s), 1 ⇐ p ⇐ 128, 0 ⇐ s ⇐ p |
| Ingres | DECIMAL(p,s), NUMERIC(p,s), 1 ⇐ p ⇐ 39, 0 ⇐ s ⇐ p |
Related Data Types in MySQL
| Data Types | |||||
|---|---|---|---|---|---|
| Integers | BIGINT | INT | MEDIUMINT | SMALLINT | TINYINT |
| Floating-point | FLOAT(p,s) | REAL(p,s) | DOUBLE(p,s) | ||
All Data Types