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

  • Linux Mint 18.2 Create Config File To Start Application Upon Login
  • Dell Wyse Thin Client BIOS Access Key
  • sudoers fle in /etc warning about comments/includes!
  • Centos 7 Reallocate logical volume space to another
  • lvm how to reduce volume size
  • letsencrypt certbot error "Unable to find a virtual host listening on port 80 which is currently needed for Certbot to prove to the CA that you control your domain. Please add a virtual host for port 80."
  • SSH error cannot Forward or Listen "bind: Cannot assign requested address"
  • X11 SSH Linux Forwarding Error
  • Using Microsoft's Group Policy Management Editor (GPME) To Change Default Permissions and Internet Explorer Security Settings
  • Thunderbird How To Disable An Email Account
  • iptables linux firewall recommended rules for public computing
  • LVM How To Create Pool (dynamically allocated and not fully allocated)
  • VMWare Pro Workstation Nic Disconnected and No IP Using NAT
  • Linux How To Create A RamDisk
  • mdadm force resync when resync=PENDING solution
  • Proxmox Breaks Storage/LVM Backing If Killing QEMU-IMG
  • Proxmox trying to acquire lock... TASK ERROR: can't lock file '/var/lock/qemu-server/lock-102.conf' - got timeout
  • Debian 9 SSH root password authentication failure password not working problem / solution
  • QEMU / KVM How To Manually Create Basic Virtual Machine VM
  • Linux wlan0 check all wireless clients