Found 4219 Articles for MySQLi

As we know that with the help of SHOW DATABASES statement we can see the list of MySQL databases. Similarly, we can use SHOW SCHEMAS as the synonym of SHOW DATABASES to get the list of databases.Examplemysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | gaurav             | | mysql              | | performance_schema | | query              | | query1             | | sys                | | tutorials          | +--------------------+ 8 rows in set (0.07 sec) mysql> SHOW SCHEMAS; +--------------------+ | Database           | +--------------------+ | information_schema | | gaurav             | | mysql              | | performance_schema | | query              | | query1             | | sys                | | tutorials          | +--------------------+ 8 rows in set (0.00 sec)

We can use MySQL COALESCE() function to get the first non-NULL value as output from a list of values. In other words, this function will check all the values until non-null value found. It can take one or more than one argument. It is having the following syntax:COALESCE(value1, value2, …, valueN)ExampleFollowing is an example to demonstrate it −mysql> Select COALESCE(NULL, NULL, NULL, 'Ram', 'Aarav', NULL); +--------------------------------------------------+ | COALESCE(NULL, NULL, NULL, 'Ram', 'Aarav', NULL) | +--------------------------------------------------+ | Ram                                              | +--------------------------------------------------+ 1 row in set (0.00 sec)

If all the values in MySQL COALESCE() function are NULL then it returns NULL as the output. It means that this function does not find any non-NULL value in the list.Examplemysql> Select COALESCE(NULL, NULL, NULL, NULL); +----------------------------------+ | COALESCE(NULL, NULL, NULL, NULL) | +----------------------------------+ |                             NULL | +----------------------------------+ 1 row in set (0.00 sec)

There may be something wrong which can happen to the database server e.g., the server was shutdown unexpectedly, error while writing data to the hard disk, etc. These situations could make the database operate incorrectly and in the worst case, it can be crashed.With the help of CHECK TABLE statement MySQL allows us to check the integrity of database tables. Its syntax would be as follows −CHECK TABLE table_nameHere, table_name is the name of the table.ExampleWe are running this statement for the table Student_info as follows −mysql> CHECK table student_info\G *************************** 1. row ***************************    Table: query.student_info       ... Read More

We can use MySQL absolute value i.e. ABS() function to select records from a table if the absolute value of the difference between two values is greater than a certain number. We are using the data from ‘Marks’ table to demonstrate it for example. Example mysql> Select * from studentmarks where abs(Hindi-English)>10; +--------+-------+---------+------+---------+-----------+ | Name | Hindi | English | Math | Physics | Chemistry | +--------+-------+---------+------+---------+-----------+ | Gaurav | 75 | 86 | 95 | 69 | 85 ... Read More

While working with the database, we have a tendency to do plenty of changes like insert, update and delete data within the table which will cause the physical storage of the table fragment. As a result, the performance of database server is degraded.MySQL provides us with OPTIMIZE TABLE statement that allows you to optimize the table to avoid this defragmenting problem. Its syntax would be as follows −OPTIMIZE TABLE table_nameHere, table_name is the name of the table.ExampleWe are running this statement for the table Student_info as follows −mysql> Optimize table student_info\G *************************** 1. row ***************************    Table: query.student_info       Op: ... Read More

MySQL query optimizer is an important element of the MySQL server that makes an best question execution set up for a query. For a particular query, the query optimizer uses the stored key distribution and other factors to decide the order in which tables should be joined when you performing the join, and which index should be used for a specific table.However, the key distributions can be sometimes inaccurate e.g., after you have done a lot of data changes in the table including insert, delete, or update. IIf the key distribution isn't correct, the question optimizer could pick a nasty ... Read More

As we know that an empty hexadecimal value is a zero-length binary string hence if 0 is added to it then the result would be 0. In other words, we can say that if we convert an empty hexadecimal value to a number then it produces 0. The following query will make it understand −mysql> SELECT X''+ 0; +--------+ | X''+ 0 | +--------+ | 0      | +--------+ 1 row in set (0.15 sec)

Actually, MySQL evaluates an empty hexadecimal value to a zero-length binary string. It can be demonstrated as follows −mysql> Select CHARSET(X''); +--------------+ | CHARSET(X'') | +--------------+ | binary       | +--------------+ 1 row in set (0.00 sec)The above result set shows that the empty hexadecimal value is a binary string. And the result set below shows that it is of length 0.mysql> Select LENGTH(X''); +-------------+ | LENGTH(X'') | +-------------+ | 0           | +-------------+ 1 row in set (0.00 sec)

MySQL returns NULL if we provide any non-hexadecimal number as an argument to UNHEX() function. Following example will demonstrate it.Examplemysql> Select UNHEX('ANK96598'); +-------------------+ | UNHEX('ANK96598') | +-------------------+ | NULL              | +-------------------+ 1 row in set (0.00 sec)As we know that the valid hexadecimal digits are between ‘0…9’, ‘A…F’ or ‘a…f’ hence the above query returns NULL.