How to Store a Phone Number in MySQL

Store a phone number in MySQL as a string (VARCHAR), never an integer. An integer drops leading zeros, can't hold the leading +, can't hold an extension, and you will never do arithmetic on a phone number anyway. The column you actually want is VARCHAR(16) holding the number in E.164 format: a leading +, the country code, then the national number, capped at 15 digits total by the ITU standard. Below is the comparison table, why BIGINT is the trap, a worked contacts schema with an index, and where the normalization actually happens (your application, not SQL).

Short answer: normalize every number to E.164 in your app before it touches the database, store the result in phone_e164 VARCHAR(16), and index that column for lookups and dedupe. Optionally keep the raw, as-typed input in a second VARCHAR(32) column for display. Use ascii or latin1 charset, not utf8mb4, because phone strings are pure ASCII. Do not use BIGINT.

Why a phone number is a string, not a number

This is the whole decision, so it is worth being blunt about it. A phone number looks numeric, but it is an identifier made of digits, the same way a US ZIP code or a credit-card PAN is. Treat it as text. Four concrete things break the moment you put one in an integer column:

• Leading zeros vanish. A UK number like 020 7946 0958 stored as an integer becomes 2079460958, and you have silently corrupted the data. Many national formats begin with a trunk 0.
• The + can't be stored. E.164 numbers are written with a leading + (more on that below). An integer column has no place for it, so you either drop it or you store a bare number and lose the "this is international" signal.
• Extensions don't fit. +1-555-0100 ext. 4021 has structure an integer can't represent at all.
• Arithmetic is meaningless. You never add, average, or sum phone numbers. The one thing an integer column is good at is the one thing you will never do here. Range queries, SUM(), AVG() on a phone column are all nonsense.

Once you accept it is a string, the only real question is which string format you store, and how wide the column has to be.

Normalize to E.164

The format to standardize on is E.164, the ITU-T international numbering plan. An E.164 number is the country code (1 to 3 digits) followed by the national subscriber number, and the whole thing is capped at 15 digits. In practice everyone writes it with a leading + so it's unambiguous internationally: +14155550123, +442079460958, +919876543210.

One precise point that trips people up: per the ITU spec the + is a display convention, not part of the number's digits. The 15-digit limit counts only the digits. So the storage need is: 15 digits plus the one + character equals 16 characters maximum. That is exactly why VARCHAR(16) is the right width.

E.164 is the format to store because it is canonical: every phone number has exactly one E.164 representation, no spaces, no dashes, no parentheses, no local trunk prefix. That canonicality is what makes two things work that otherwise don't: a UNIQUE index that actually catches duplicates (because (020) 7946 0958 and +44 20 7946 0958 collapse to the same +442079460958), and a fast equality lookup by phone number. Store numbers in whatever shape the user typed and neither of those is reliable.

The column: BIGINT vs VARCHAR(16) vs splitting it up

Here are the options laid against each other. The verdict column is the short version.

The split-column approach (country_code, area_code, subscriber, extension as separate fields) feels tidy and is almost always a mistake. Phone-number structure is not uniform across countries, so you end up writing per-country parsing logic anyway, and a unique constraint across four columns is clumsier than one on a single canonical string. Parse the number in your app, yes, but store the normalized result as one value. The single E.164 column is the cleanest design for the overwhelming majority of cases.

If you also need the extension or the human-formatted version, widen to VARCHAR(32) (or store the extension in its own small column). But keep the E.164 value pure in VARCHAR(16) and put the messy stuff elsewhere, so the indexed lookup column stays canonical.

A worked schema

A minimal contacts table. The normalized number lives in phone_e164, indexed for lookups and uniqueness. The raw, as-entered input is kept alongside for display, so you can show users the number the way they typed it without losing the canonical form.

CREATE TABLE contacts (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(120) NOT NULL,
  phone_e164  VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  phone_raw   VARCHAR(32) NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_contacts_phone (phone_e164)
) ENGINE=InnoDB;

-- the app normalized "(415) 555-0123" to E.164 before this insert
INSERT INTO contacts (name, phone_e164, phone_raw)
VALUES ('Ada', '+14155550123', '(415) 555-0123');

-- lookup by phone number: compare canonical to canonical
SELECT id, name FROM contacts WHERE phone_e164 = ?;

A few choices worth calling out. phone_e164 is CHARACTER SET ascii COLLATE ascii_bin because a phone number is pure ASCII: digits and a +. Using utf8mb4 here would reserve up to four bytes per character in the index for characters that can never occur, wasting index budget for nothing. The ascii_bin collation also makes comparisons exact-byte, which is what you want for an identifier. If you don't need to enforce uniqueness (a contact can have the same number twice for some reason), swap the UNIQUE KEY for a plain INDEX idx_contacts_phone (phone_e164); either way you want it indexed so the lookup above is a B-tree seek, not a full scan.

This schema runs unchanged on MariaDB. Nothing here is MySQL-specific: VARCHAR, the charset and collation, the index, and the ? parameter all behave the same way, so storing a phone number in MariaDB is identical to storing it in MySQL.

Normalize in your app, not in SQL

The one thing MySQL can't do well is the normalization itself. Turning (415) 555-0123, 415.555.0123, and +1 415-555-0123 all into +14155550123 requires knowing the default country, the national dialing rules, and which numbers are even valid. That is a job for a real phone-number library, run in your application before the INSERT:

• Google's libphonenumber is the reference implementation, with ports and bindings for most languages. It parses a raw string plus a default region, validates it, and emits the E.164 string.
• PHP: giggsey/libphonenumber-for-php (a direct port).
• Python: the phonenumbers package.
• Node.js: libphonenumber-js or google-libphonenumber.

The flow is the same everywhere: take the user's raw input and their country (from a country selector, or geo-IP, or the account's locale), parse it to an E.164 string, reject it if the library says it's not a valid number, and only then store the canonical value in phone_e164. Keep the original string in phone_raw if you want to echo it back exactly as typed. Validating at write time is far cheaper than discovering six months later that the column is full of garbage that won't dedupe.

For sizing every other column in that table, see MySQL data types and sizes, and for the VARCHAR and charset syntax on one page the MySQL cheat sheet has the DDL.

FAQ

See also

• Same identifier-as-string reasoning for storing an email address in MySQL, where canonicalization and a unique index matter for the same reasons.
• Storing an IP address in MySQL is the other "looks numeric, store it deliberately" case, with a different right answer.
• The MySQL field types and sizes reference for picking widths and byte costs on every other column in the table.
• If you are migrating an existing BIGINT phone column, changing a column type in MySQL walks through the ALTER TABLE and the data-loss traps.
• Need a throwaway instance to test the schema against? See running MySQL in Docker.
• The MySQL cheat sheet collects the VARCHAR, charset, and index DDL on one page.