Skip to main content

MySQL: Int(20) data-type : Is it similar to Bigint?

MySql : Int(11), Int(11 +number)

If we're using Int(11) as a data-type in MySql then by default it is signed integer range between (-2147483648) and (2147483647)
It's same as any object oriented programming language's "Integer" datatype, means it'll take "4 byte" storage
If we make this "Int(11) as unsigned number" then it ranges between (0 and 4294967295)

Example:

  1. First let's create a table

 CREATE TABLE TableToDelete (
  FullName VARCHAR(256) DEFAULT NULL,
  SignedId INT(11) DEFAULT NULL,
  UnsignedId INT(11) UNSIGNED DEFAULT NULL,
  IdIntMoreThan11 INT(20) DEFAULT NULL,
  IdZerofill INT(20) UNSIGNED ZEROFILL DEFAULT NULL
)

   2. Insert some records with value, let's say "1234567891234567"
INSERT INTO TableToDelete (SignedId, UnsignedId, IdIntMorethan11, IdZerofill)
VALUES (1234567891234567, 1234567891234567, 1234567891234567, 1234567891234567)

because number : "1234567891234567" exceeds the max length of "signed integer : 2147483647" as well as of "unsigned integer : 4294967295". This insert statement will get executed successfully (without any error).

 Here our inserted records will be
SignedId -> 2147483647
UnsignedId -> 4294967295
IdIntMoreThan11 -> 2147483647
IdZerofill -> 00000000004294967295

So what is the meaning of declaring "Int(20) or something like Int(11 + number)"?
In this case we've declared column "IdZerofill" with  "INT(20)"  as  "UNSIGNED ZEROFILL DEFAULT NULL"
mean what ever the value we want to insert into "IdZeroFill" column we can insert as we used to insert with normal int data-type, but MySql will pad it with "0" on left side. In this case you can see the result in "IdZeroFill" as "00000000004294967295" which is left padded by "0"

  If we're not including "zerofill" keyword while declaring it, it'll behave like normal int datatype.

Conclusion:

   "int(20)" means left pad it with "zero", if we specify the "ZEROFILL" during it's declaration otherwise it behaves like normal "int" datatype.


Comments

Post a Comment

Popular posts from this blog

EFCore - Collate function

Search in SQL server query is generally case insensitive (by default or based on database level collation). Suppose we have an employees table with a row having first-name column value as "My-First-Name", so if we want to do the case-sensitive search we have to explicitly use the related collate: In EF 5 (currently in Release Candidate version [RC.2.20475.6]) Collate function got introduced which helps us to use our specific collation based search.  C# with EF5 code sample: var employeeCaseSensitiveSearch = _dbContext.Employees .Where(x => EF.Functions.Collate(x.FirstName, "Latin1_General_CS_AS") == "my-first-name") .FirstOrDefault(); A related database query will be something like this: T-SQL: Case sensitive search (use specific collation e.g.: Latin1_General_CS_AS) SELECT * FROM dbo.Employees AS e WHERE e.FirstName Collate Latin1_General_CS_AS = 'my-first-name' Some of the useful CSharp function which g...
Post a Comment
Read more

Azure SQL - User management with Active Directory Group

To manage the user's roles we use Service Account on OnPrem/IaaS servers. We can use this service account on our SQL Server and accordingly manage users' permissions for the users who are part of this service account. Suppose we have an Azure SQL and we want to manage set-of-users permission shall we create each-and-every users' user profile in Azure SQL and set the permissions accordingly. Obviously, we will not .  If we've anything like Service Account (or Group) on Azure we could create the user of this GROUP on Azure SQL and set its permission/role. Here comes the Azure Active Directory Group to help us.  In case if our organization has some scheduled tasks to sync Active Directory (service account) to Azure Active Directory (AAD group), already in place, there will be some delay when we add a user to the Service account and get reflected in the AAD Group. If we have a requirement that as soon as we add a user to our group we wan...
Post a Comment
Read more

Azure VM - Failed to update virtual machine disks (Solved)

Sometimes while trying to add/remove a disk from Azure VM through the Azure portal we are getting error like " Failed to update virtual machine disks " as below Here though we are only trying to add/remove the disk, we're not doing anything with Azure Disk Encryption (ADE), still, we're getting the error message related to ADE.  Not while adding or updating the disk, if we try to add extensions or performing any activity required to do save operation to update the VM template, it show a similar failure message which is related to ADE.  Now to resolve this if we disable the disk encryption through the Azure portal through it will disable the disk encryption and on portal, we can see there is no disk encryption and also the related Key vault field is coming up empty but still, when we try to add/remove the disk & do the save operation we are getting the similar message. This is because in the VM JSON template we can se...
1 comment
Read more