Skip to main content

MySQL: ENUM user defined data-type


In any object oriented programming language ENUM is frequently used "user defined datatype" (UDD), but not every RDBMS databases support ENUM.

MySQL supports ENUM data-type. Let's see how it behaves in the context of MySQL.


-- Step1. create table with ENUM datatype
-- 1a. drop table if already exists
DROP TABLE IF EXISTS delete_enum_datatype;

-- 1b. Create table with default values (1. large, 2. small, 3. x-large)
 CREATE TABLE delete_enum_datatype (fittings ENUM('large', 'small', 'x-large'))


-- Step2. Insert some Dummy data (already defined in DDL: 'large', 'small', 'Small' with capital S \
--            not in table definition: 'big'
 INSERT INTO delete_enum_datatype(fittings) VALUES ('large'), ('big'), ('small'), ('Small')


 Point To Ponder: MySql internally stores the key of the ENUM values as integer data-types.
                It initialize the key (integer) to the "values" in the order in which we define our values at the time of table creation.
In this case we defined the "fittings" column data-type in the order ('large', 'small', 'x-large','Small')
So MySQL internally associate key in this way
1:large
2:small
3:x-large
2: Small (because we already defined 'small', and MySQL stored all ENUM values in small letters
          , so it associate the key "2" to this value which is already defined)
   
-- How to access the ENUM data-type

MySQL provides us two way to select the ENUM data-typed column values
Let's say we want to select the values from table where "fittings" is 'small', 'large'

1. SELECT * FROM delete_enum_datatype WHERE fittings IN (1,2)
2. SELECT * FROM delete_enum_datatype WHERE fittings IN ('large', 'small')

both of these queries will give you same result-set.


NOTE:
1. If you're inserting any ENUM data which are not defined during table creation, MySQL will put the empty string and the corresponding Key value will be 0

SELECT * FROM delete_enum_datatype WHERE fittings = 'big'
or
SELECT * FROM delete_enum_datatype WHERE fittings = 0

2. MySql will store the ENUM data in small letters (based on your database settings) irrespective of the value being passed in the insert statement.
  Let's say you're inserting a record for small in the following ways ('Small', 'SMaLL', 'smALl'). Each of these values will get stored in small letters only.




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

Git - Update submodule

Sometimes in the large project when we separate it in different repositories we need to take reference of one repository in another where some the sub-module concepts. Now if we move our submodule to a different repository or renaming its repo-path then we've to also update the repository where this sub-module is getting referenced. Let's see how to update the git submodule.  Step-1: First clone the repo Step-2: Open git UI to create a new local branch from master  Step-3: Open git bash -- Now run below command to (remove submodules & add them):  -- Remove submodule git submodule deinit YOUR_FIRST_REPO git rm YOUR_FIRST_REPO git commit -m "Removed submodule YOUR_FIRST_REPO" rm -rf .git/modules/YOUR_FIRST_REPO -- Add submodule git submodule add <<..YOUR_FIRST_REPO git URL>> Step-4: All good, let's push our changes to the master
Post a Comment
Read more

EFCore - Update command is not updating the underlying data

Why my update command using EFCore is not updating the underlying data in the database though in middle-tier code it's successful, not throwing any error? Let's try to get the root cause and how to solve this.  When creating the model class of related database object if the primary key name is Id then no need to explicitly mention the Key in ef-core OnModelCreating.  But if we've key name different from Id then we have to explicitly mention it in our OnModelCreating. Something like this:  entity.HasKey(c => new {c.CustId }); Assume in the database we've Customers table like this: CREATE TABLE [dbo].[Customers]( [CustId] [INT] IDENTITY(1,1) NOT NULL Primary Key, [FirstName] [VARCHAR](50) NULL ) Point to ponder: In the database table, column CustId is already defined as Primary key.  In EFCore model setup, it's up to us to configure the Key as (custId) or configure it as HasNoKey() Assume we've set our entity with Ha...
Post a Comment
Read more