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

How to install Zen-coding plugin

As a web-developer, irrespective of the technologies (java, c-sharp, python, php e.t.c.…), we used to write CSS code to make our web-pages looks good if not at least we’re involved in write html codes. What if there is some tool to whom you give some instruction and that tool generates a good, well formatted html tags for you. These kind of coding is possible and known as “ Zen coding ” and there are lots of plug-in available from different vendors. We’re going to  discuss the steps to install this “zen-coding” plugin for “visual studio, eclipse, sublime-text & notepad++ in next few lines. Follow the below steps to install "zen-coding" plugin based on your editor.  Steps to install zen-coding plugin for (visual studio, eclipse, sublime-text,notepad++) -- -- For Visual Studio 1. Go to "Tools" -> "Extensions and Updates" 2. It'll open the "Extensions and Updates windows"    Select online form Left hand menu items ent...
Post a Comment
Read more

EFCore - Scaffold-DbContext how to use

EFCore versions, we don't have GUI to manage the DB-entities & dbContext like we used to have with EF6 (or EF5/4 version). With EFCore we've the Scaffold-DbContext command which helps us to create entities & DB context from our DB-objects.  Let's see how to use Scaffold-DbContext: Assume we're connecting with SQL Server so after creating a sample project you'll have to install EFCore related libraries as mentioned below.  Install entity-framework-core for SQL Server, at this time 3.1.9 is the latest stable version   Microsoft.EntityFrameworkcore   Microsoft.EntityFrameworkCore.SqlServer  To use Scaffold-DbContext, following two NuGet packages are required Microsoft.EntityFrameworkCore.Tools  Microsoft.EntityFrameworkCore.Design Let's say we've two DB tables (e.g. dbo.Employees & dbo.Users )  Now let's see how to use Scaffold-DbContext to create c-...
Post a Comment
Read more