MySQLi Articles - Page 366 of 388

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 More

In case if all the arguments (list of strings) of FIELD() function are NULL then MySQL will return 0 as output.Examplemysql> Select FIELD('Ram',NULL,NULL,NULL,NULL); +----------------------------------+ | FIELD('Ram',NULL,NULL,NULL,NULL) | +----------------------------------+ |                               0  | +----------------------------------+ 1 row in set (0.00 sec)

As we know that NULL fails equality comparison with any value hence if the search string, provided in FIELD() function, is NULL then MySQL returns 0 as output.Examplemysql> Select FIELD(NULL,'Ram','is','good','boy'); +-------------------------------------+ | FIELD(NULL,'Ram','is','good','boy') | +-------------------------------------+ |                                   0 | +-------------------------------------+ 1 row in set (0.00 sec)

Suppose if the search string is not in the list of strings provided as the arguments in FIELD() function then MySQL will return 0 as output.Examplemysql> Select FIELD('Ram','New','Delhi'); +----------------------------+ | FIELD('Ram','New','Delhi') | +----------------------------+ |                         0  | +----------------------------+ 1 row in set (0.00 sec)

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 More

When we use CONCAT_WS() function with WHERE clause then the output would be based upon the condition provided in WHERE clause. It can be understood from the example of ‘Student’ table as followsExamplemysql> Select CONCAT_WS(' ',Name, Last_name, 'Resident of', Address, 'is studying', Subject)AS 'Student Detail' from student WHERE id = 20; +----------------------------------------------------------------+ | Student Detail                                                 | +----------------------------------------------------------------+ | Gaurav Rathore Resident of Jaipur is studying Computers        | +----------------------------------------------------------------+ 1 row in set (0.00 sec)

NULL as both argumentsMySQL returns blank output if we will use NULL as both of the arguments in CONCAT_WS() function.Examplemysql> Select CONCAT_WS('', NULL, NULL); +-------------------------+ | CONCAT_WS('', NULL, NULL) | +-------------------------+ |                         | +-------------------------+ 1 row in set (0.00 sec)NULL as one of the argumentMySQL returns the value of the other argument as output if we will use NULL as one of the argument in CONCAT_WS() function.Examplemysql> Select CONCAT_WS('', NULL, 'Delhi'); +----------------------------+ | CONCAT_WS('', NULL, 'Delhi') | +----------------------------+ | Delhi                 ... Read More

As we know TEXT data objects are useful for storing long-form text strings. The different TEXT objects offer a range of storage space from 255 bytes to 4 Gb. The following table shows the storage of different kinds of TEXT data type −Type of BLOBMaximum amount of Data that can be storedOverheadTINYTEXTUp to 255 bytes1 byteTEXTUp to 64 Kb2 bytes MEDIUMTEXTUp to 16 Mb3 bytesLONGTEXTUp to 4 Gb4 bytes

We can use the output of CONCAT() function which is applied to the column/s of a MySQL with the column/s of another MySQL table. It can be done with the help of MySQL join.ExampleFor example, we have two table ‘Student’, having the details like id, Name, Last_name, Address and Subjects of the students, and ‘Remarks’, having the id and comments about the students. Now, the following query can combine CONCAT() function with another table column −mysql> Select * from remarks; +------+-------------+ | ID   | Comment     | +------+-------------+ | 1    | Good        | | ... Read More

As we know that BLOB is a binary large object that can hold a variable amount of data. The different TEXT objects offer a range of storage space from 255 bytes to 4 Gb. Following table shows the storage of different kinds of BLOB data type −Type of BLOBMaximum amount of Data that can be storedOverhead TINYBLOBUp to 255 bytes1 byteBLOBUp to 64 Kb2 bytes MEDIUMBLOBUp to 16 Mb3 bytes LONGBLOBUp to 4 Gb1 Bytes