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

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

Azure SQL - Re-build index and update stats

Useful script to rebuild table indexes and update stats You can update the queries based on your requirement. To update the table stats --Query to update the table stats SELECT N'UPDATE STATISTICS ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' ;' FROM INFORMATION_SCHEMA.tables AS t WHERE t.TABLE_TYPE = N'BASE table'; GO To re-build table indexes: --To rebuild table index DECLARE @myDatabaseName NVARCHAR(255) = N'your-database-name'; DECLARE @runningTableName NVARCHAR(255); DECLARE @cmd NVARCHAR(1000); DECLARE @name NVARCHAR(100)= 'your-database-name'; -- Populate the database cursor. DECLARE myDatabaseLevelCursor CURSOR READ_ONLY FOR SELECT @name; OPEN myDatabaseLevelCursor; FETCH NEXT FROM myDatabaseLevelCursor INTO @myDatabaseName; WHILE @@FETCH_STATUS = 0 BEGIN -- In this command we're declaring table level cursor SET @cmd = 'DECLARE myTableCursor CURSOR READ_ONLY FOR SELECT '...
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