MySQL Maximum INT Size Truncation Issue/Warning

MySQL will silently truncate a larger INT than capable.

Check MySQL's own documentation here:

As we can see the maximum size of INT (which is the most commonly used) is 2147483647

A lot of coders make this mistake by using very large values such as 9999999999 but it would actually truncate to 2147483647 which is the maximum size of an INT.  This is dangerous because any value over that would truncate to that exact maximum number causing duplicate or unintended entries.


 

Type Storage Minimum Value Maximum Value
  (Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
    0 255
SMALLINT 2 -32768 32767
    0 65535
MEDIUMINT 3 -8388608 8388607
    0 16777215
INT 4 -2147483648 2147483647
    0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
    0 1844674407370955161

 


Tags:

mysql, maximum, int, truncation, warningmysql, silently, truncate, larger, documentation, commonly, coders, duplicate, unintended, entries, bytes, unsigned, tinyint, smallint, mediumint, bigint,

Latest Articles

  • SSH Keep Alive To stop Disconnections
  • Linux How To Disable SATA NCQ For Better Performance
  • the sign-in method you're trying to use isn't allowed. For more info, contact your network administrator - solution for active directory
  • gsmartcontrol for Windows to Check the SMART S.M.A.R.T status
  • WebRTC Vulnerability Shows Local IP Address Even When Using a Proxy or VPN Firefox Fix And Disable Solution
  • chroot in Linux Howto Simple and Easy Guide
  • qemu-kvm qemu-system Image format was not specified for '/mnt/space/cucm12.img' and probing guessed raw. Automatically detecting the format is dangerous for raw images, write operations on block 0 will be restricted. Specify the 'ra
  • Linux Over VNC VMWare How To Switch Virtual Terminals Console Without Using Ctrl+Alt+F1
  • Skype For Business 2015 and 2019 Guide, Reference, Howto and Troubleshooting Solutions
  • Centos 6 or 7 no DHCP IP during startup on first boot or reboot solution
  • Debian / Mint / Ubuntu net-tools packages provides netstat, ifconfig, route, arp and other classic network admin tools
  • Linux Mint XWindows Ubuntu MATE or Cinnamon How To Restart The GUI / Graphics / Session if it freezes without losing current windows or programs
  • Linux bash prompt why does it not show username@host and the current directory?
  • Microsoft SQL Server Check What Version is Running
  • How to install and setup LXC Containers (OpenVZ alternative) on Centos 6 / 7
  • Cisco CUCM Unified Communication Manager Howto Guide and Tutorials
  • SSH persistent and automatic login script for proxy
  • SSH proxy/command in the background or from cron script
  • ssh reverse proxy to enable remote access behind a LAN and firewall
  • How To Use Letsencrypt SSL/TLS Encryption to Create Certificates without installing on the target machine