Found 4219 Articles for MySQLi

We can do it with the help of the DEFAULT attribute of the ENUM data type. The DEFAULT attribute causes an ENUM data type to have a default value when a value is not specified. In other words, we can say that INSERT statement does not have to include a value for this field because if it does not include then the value following DEFAULT will be inserted. Functions are not allowed in the DEFAULT expression. For ENUM data type the DEFAULT values include NULL and empty string (‘’).Examplemysql> Create table enum123(Rollno INT, Name Varchar(20), result ENUM('Pass', 'Fail') DEFAULT 'Fail'); ... Read More

MySQL stores ENUM values internally as integer keys (index numbers) to reference ENUM members. The main reason for not storing the integer values in ENUM column is that it is very obvious that MySQL ends up referencing the index instead of the value and vice-versa.ExampleFollowing example can clarify it −mysql> Create table enmtest(Val ENUM('0', '1', '2')); Query OK, 0 rows affected (0.18 sec) mysql> Insert into enmtest values('1'), (1); Query OK, 2 rows affected (0.19 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> Select * from enmtest; +-----+ | Val | +-----+ | 1   | | 0 ... Read More

As we know the 1st argument of ELT() function must be an integer value but when we provide index number which is not an integer the MySQL ELT() function returns NULL with a warning.Examplemysql> select ELT('one','Ram,is,good,boy')As Result; +--------+ | Result | +--------+ | NULL   | +--------+ 1 row in set, 1 warning (0.00 sec) mysql> Show Warnings; +---------+------+------------------------------------------+ | Level   | Code | Message                                  | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'one' | +---------+------+------------------------------------------+ 1 row in set (0.00 sec)

As we know that enumeration values are associated with index values hence if we will use enumeration values in an expression then all the calculations would be done on index numbers. The following example will clarify it −mysql> Select * from Result; +-----+--------+-------+ | Id  | Name   | Grade | +-----+--------+-------+ | 100 | Gaurav | GOOD  | | 101 | Rahul  | POOR  | | 102 | Rahul  | NULL  | | 103 | Mohan  |       | +-----+--------+-------+ 4 rows in set (0.00 sec) mysql> Select SUM(Grade) from result; +------------+ | SUM(Grade) | +------------+ ... Read More

MySQL ELT() function returns NULL as output if the index number provided as argument is higher than the number of strings. Following is an example to make it clearer −Examplemysql> Select ELT(6,'Ram','is','a','good','boy')As Result; +--------+ | Result | +--------+ | NULL   | +--------+ 1 row in set (0.00 sec)As we can see that index number is 6 and the list of strings is having only 5 strings. Hence MySQL returns NULL.

As we know, both the functions are used to search a string from the arguments provided in them but there are some significant differences between them as follows −FIND_IN_SET() −  function uses the string list that is itself a string containing the substring separated by commas. Whereas, FIELD() function contains list of different strings among which it will find the index number of the string, if present, which is to be searched.FIND_IN_SET() −  function returns NULL if any of the argument i.e. either search string or string list is NULL. In contrast, FIELD() function do not returns NULL but returns ... Read More

FIND_IN_SET() function returns NULL as output if any of the argument i.e. either search string or string list, is NULL. Of course, It will also return NULL if both of the arguments are NULL.Examplemysql> Select FIND_IN_SET(NULL,'Ram is a good boy') AS Result; +--------+ | Result | +--------+ | NULL   | +--------+ 1 row in set (0.00 sec) mysql> SELECT FIND_IN_SET('RAM',NULL)AS RESULT; +--------+ | RESULT | +--------+ | NULL   | +--------+ 1 row in set (0.00 sec) mysql> SELECT FIND_IN_SET(NULL,NULL); +------------------------+ | FIND_IN_SET(NULL,NULL) | +------------------------+ |                   NULL | +------------------------+ 1 row in set (0.00 sec)

When we use FIND_IN_SET() function in WHERE clause then it searches the search string within the given string as specified in the argument and retrieves all the columns from concerned rows. Following is an example to demonstrate it −ExampleIn this example, we are getting the columns from ‘Student’ table where the rows have the value of name as ‘Gaurav’. Here the FIND_IN_SET() function will search the search string ‘Gaurav’ from the values of column ‘Name’.mysql> Select Id, Name, Address, Subject from student WHERE FIND_IN_SET('Gaurav', Name); +------+--------+---------+-----------+ | Id   | Name   | Address | Subject   | +------+--------+---------+-----------+ ... Read More

There will be a significant change in the output if we have the combination of NULL and other values in the list of strings, provided as arguments in FIELD() function. Following example will demonstrate it Example mysql> Select FIELD('good', 'Ram', 'is', 'good', 'boy'); +---------------------------------------+ | FIELD('good', 'Ram', 'is', 'good', 'boy') | +---------------------------------------+ | 3 | +---------------------------------------+ 1 row in set (0.00 sec) ... Read More

If strict SQL mode is disabled and we insert invalid value (which is not in the list of permitted enumeration values) into ENUM then MySQL will insert an empty string instead of throwing an error. But if strict SQL mode is enabled then MySQL throws an error on inserting invalid value.ExampleAfter disabling the strict SQL mode, we insert the invalid string into ENUM as follows −mysql> Insert into result(id, name, grade) values(100, 'Gaurav', 'abcd'); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> Select * from result; +-----+--------+-------+ | Id | Name    | Grade | +-----+--------+-------+ | ... Read More