含短横线的数字串在MySQL中应选用何种正确数据类型？

Hey Steven, great question—let's break down the best options for your hyphenated numeric strings like '405-6444235-1711500':

First, let's rule out the obvious bad choices: don't use numeric types like TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT. These types are built for pure numeric values, not formatted strings with hyphens. If you tried storing your hyphenated string in one of these columns, MySQL would strip out the hyphens entirely and store only 40564442351711500—destroying the original formatted structure you need to preserve. Even if the numeric value fits within a BIGINT's range, you lose the critical hyphen separators that are part of your data's identity.

Now, let's look at the right choices:

• VARCHAR (recommended for most cases)
  This is the go-to if you just need to store and display the exact hyphenated string, with no need to perform numeric calculations on the whole string or its segments. Pick a length slightly longer than your longest possible string—for your example (19 characters total), VARCHAR(20) works perfectly, giving you a small buffer for any future length adjustments.

• CHAR (if all strings are fixed-length)
  If every hyphenated string you'll store has the exact same character count (like your example's 19 characters), CHAR(19) is a more efficient option. Fixed-length CHAR columns have faster read/write performance compared to VARCHAR, but only use this if you're 100% sure the string length will never vary.

• Split into separate numeric columns (if you need to work with segments)
  If the hyphens divide the string into meaningful numeric parts (e.g., 405 is a region code, 6444235 is an account number, 1711500 is a sequence), you could split the string into three dedicated columns:

  • part1 INT (for the 3-digit segment)
  • part2 BIGINT (for the 7-digit segment)
  • part3 BIGINT (for the final 7-digit segment)
    This makes it easier to query or calculate with individual segments, but adds complexity—only do this if you actually need to interact with those parts as numbers.

Final Recommendation #

For most scenarios where you just need to preserve the exact hyphenated string, VARCHAR(20) is the safest and most flexible choice. Numeric types are a hard pass here—they'll corrupt your formatted data.

内容的提问来源于stack exchange，提问作者Steven Smith