Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQL Articles
Page 2 of 355
MySQL query to select ENUM(\\\'M\\\', \\\'F\\\') as \\\'Male\\\' or \\\'Female\\\'?
In MySQL, the ENUM data type is used to limit column values to a specified set of options which is useful for categories like colors, status, and gender. Sometimes, it is helpful to display these ENUM values as more descriptive like converting 'P' to "pass" and 'F' to "Fail". The IF() function in MySQL makes this easy by allowing conditional logic within SQL queries. The example below will demonstrate how to use the IF() function to convert ENUM values into user-friendly. Example To understand first let us create a table, DemoTable, which contains an ENUM column UserGender to store ...
Read MoreIn which order MySQL will invoke the triggers if we created multiple triggers of same event and action time?
In MySQL, triggers allow automatic execution of specified actions in response to INSERT, UPDATE or DELETE events on a table. Often, multiple triggers may be created for the same event and action time (e.g.; multiple BEFORE INSERT triggers on the same table). By default, MySQL invokes these triggers in the order they have created. However, the FOLLOWS and PRECEDES option allows control over the sequence of execution which can be critical in complex data handling. In this article, we will explore how to set the order of multiple triggers for the same event and action ...
Read MoreHow to show that each MySQL enumeration has an index value?
In MySQL, the ENUM data type enables you to define a column using only a collection of predetermined values. Each value in the ENUM list is assigned a position number known as an index (which begins with 1). These index numbers represent the positions of the values in the list, not the actual data. For example, if the ENUM list is ('Male', 'Female', 'Other'), Male has an index of 1 while Female has an index of 2 and Other will have an index of 3. This further enables MySQL to store and compare the values more effectively, though actual ...
Read MoreHow to convert rows into columns in MySQL?
Converting rows into columns also known as pivoting is a common requirement in SQL. Pivoting transforms data to present it in a more organized, user-friendly format, especially for reporting or analytics purposes. In this article, we’ll explore the different methods available in MySQL to achieve this transformation. Let’s consider a sample table named sales: Year Region ...
Read MoreHow MySQL handles the empty and null values for enumerations?
In MySQL, the ENUM data type allows you to create a column with specified values like 'A', 'B', 'C', and 'D'. This feature is useful for maintaining data consistency, as it restricts entry to a specific set of values. However, the behavior of ENUM columns can vary depending on whether the NOT NULL constraints are applied and also depends on SQL modes. How to handle ENUM values with SQL modes MySQL accepts empty values for enumeration only if SQL mode is not set as TRADITIONAL, STRICT_TRANS_TABLES, or, STRICT_ALL_TABLES. Otherwise, MySQL would not accept empty values and throw an error. ...
Read MoreHow is it possible for a MySQL trigger to execute multiple statements?
In MySQL, triggers are a mechanism that allow for specific actions to be executed automatically when specific events like Insert, Update, or Delete occur in a table. They are useful as they automate processes and maintain data integrity. Using BEGIN...END in trigger In a trigger, if we want to perform more than one action we write it within the BEGIN…END block. This block simplifies the way we can manage a larger number of actions for a more complicated task. Let us see how the BEGIN...END construct allows multiple statements to be executed within a trigger. We can group ...
Read MoreHow to get all the MySQL triggers and the triggers for only the current database
Triggers are automatic operations that MySQL performs when something happens in a table, like adding a new record (INSERT), changing a record (UPDATE), or getting rid of a record (DELETE). They come in handy because they can handle repetitive tasks and make sure the info in your database is consistent and accurate without any manual interference. Listing All Triggers in MySQL To list all the triggers in MYSQL we use information_schema.triggers table. This table stores the metadata, which means the details like the trigger name, the action it responds to (INSERT, UPDATE, DELETE), and the table it is associated with. ...
Read MoreHow to insert DATE in MySQL table with TRIGGERS?
Triggers are a great feature in MySQL that allows the automation of processes either before or after data has changed in a table. An important application of the usage of triggers is for automatic date addition to the database. This article guides you on how to add the current date in MySQL using triggers every time a new record is being inserted. The following examples uses a BEFORE INSERT trigger in setting a date within a column of a table automatically. BEFORE INSERT trigger This trigger is executed right before a value is inserted into a database table. Whenever ...
Read MoreHow to set delay for MySQL trigger/procedure execution?
When we want to add a pause in MySQL triggers or stored procedures, we use the SLEEP() function. This function stops the execution of a query or procedure for a set amount of time. It is useful in different scenarios such as checking how an app acts when operations slow down or stimulating network slowness. SLEEP() Function MySQL has this SLEEP() function that will suspend the operation for a specified time duration. For example, if you want to pause for 5 seconds, you would write - SLEEP(5); This causes the database to wait for 5 ...
Read MoreEnum with NOT NULL in a MySQL field?
In MySQL, the ENUM is a data type that is used to create a column with a fixed set of values. It is helpful for the fields that have limited alternatives, such as categories, statuses, and color codes. In the ENUM data type, if you do not declare NOT NULL then it gives the default value NULL or Empty String('') when inserting records without specifying a value. However, if you declare NOT NULL and don't specify the values when inserting then it gives the first value from the ENUM. Following is the syntax to create a table that has ...
Read More