Found 4219 Articles for MySQLi

MySQL BIT_LENGTH() string function is used to get the length of the string in bits.SyntaxBIT_LENGTH(Str)Here Str, the argument of BIT_LENGTH() function, is the string whose BIT_LENGTH value is to be retrieved. Str can be a character string or number string. If it is a character string then it must be in quotes.Examplemysql> Select BIT_LENGTH('New Delhi'); +-------------------------+ | BIT_LENGTH('New Delhi') | +-------------------------+ | 72                      | +-------------------------+ 1 row in set (0.00 sec) mysql> select BIT_LENGTH(123456); +--------------------+ | BIT_LENGTH(123456) | +--------------------+ | 48                 | +--------------------+ 1 row in set (0.00 sec)

When BIN() string function is used with WHERE clause, the output returns by it will depend upon the condition given in WHERE clause. In this case, we must have to use binary value in WHERE clause. For example, suppose we have a table named ‘Student’ and we want to get only those rows where the binary value of column ‘id’ is higher than 1010, then we can write following query −mysql> Select *, Bin(id) from student where BIN(id) > 1010 ; +------+---------+---------+-----------+---------+ | Id   | Name    | Address | Subject   | Bin(id) | +------+---------+---------+-----------+---------+ | 15   ... Read More

Last_Insert_Id() MySQL function is used to find out which sequence number was assigned recently by AUTO_INCREMENT.Examplemysql> Create table Employee(Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Name Varchar(5)); Query OK, 0 rows affected (0.13 sec) mysql> Insert into Employee(Name) Values('Harvinder'); Query OK, 1 row affected (0.06 sec) mysql> Insert into Employee(Name) Values('Suresh'); Query OK, 1 row affected (0.07 sec) mysql> Select* from Employee; +----+---------+ | Id | Name    | +----+---------+ | 1  |Harvinder| | 2  | Suresh  | +----+---------+ 2 rows in set (0.00 sec) mysql> Select Last_insert_id(); +------------------+ | Last_insert_id() | +------------------+ |                2 | +------------------+ 1 row in set (0.00 sec)

With the help of the following command we can force MySQL out of TRADITIONAL mode −mysql> Set SQL_MODE =''; Query OK, 0 rows affected (0.00 sec)

In case of enabled SQL strict mode, subtraction between integers value in which one is of UNSIGNED type will produce an unsigned result by default. But MySQL produces an error if the result is a negative one. It can be observed with the following example −mysql> SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> Select CAST(0 AS UNSIGNED) -1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'The error after the query above shows that it is an overflow after numeric arithmetic expression.Now, it can be handled with ... Read More

MySQL exact-value arithmetic can handle overflow occurs during numerical expression assessment because overflow occurs depends on the range of the operands. The values used in arithmetic expressions changed to other data type can put away the overflow.For example, after converting the BIGINT Maximum value to DECIMAL while adding 1 to it can handle the overflow as follows −mysql> Select 9223372036854775807.0 + 1; +---------------------------+ | 9223372036854775807.0 + 1 | +---------------------------+ | 9223372036854775808.0     | +---------------------------+ 1 row in set (0.01 sec)

As we know that BIN() function returns the binary string of a number, of the DECIMAL base, after converting it into a binary value. In this way, it can be considered same as CONV(N, 10, 2) function. It means the output of CONV(N, 10, 2) would be same as the output of BIN() function.In CONV(N, 10, 2) function, ‘N’ is the number which will be converted, 10 represents the base, i.e. DECIMAL, of N and 2 represents that we want to convert N into a binary string.ExampleThe example below will demonstrate that the output return by BIN() is same as ... Read More

MySQL CAST can handle overflow occurs during numerical expression assessment. Suppose if numeric expression evaluation produces overflow then MySQL reflects an error message. Now to handle this overflow we can change that numeric value to UNSIGNED with the help of CAST.For example on adding 1 to BIGINT maximum value, MySQL produce an error due to overflow as follows −mysql> Select 9223372036854775807 + 1; ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807+1)'Now, with the help of CAST, MySQL handles this kind of overflow as follows:mysql> Select CAST(9223372036854775807 AS UNSIGNED) +1; +------------------------------------------+ | CAST(9223372036854775807 AS UNSIGNED) +1 | +------------------------------------------+ | 9223372036854775808                      | +------------------------------------------+ 1 row in set (0.07 sec)

In this case, the output of CHAR_LENGTH() function depends on the condition that whether we are providing NULL as a string or we are providing simply NULL to it. Following example will demonstrate the difference −mysql> Select CHAR_LENGTH(NULL); +-------------------+ | CHAR_LENGTH(NULL) | +-------------------+ | NULL              | +-------------------+ 1 row in set (0.00 sec) mysql> Select CHAR_LENGTH('NULL'); +---------------------+ | CHAR_LENGTH('NULL') | +---------------------+ | 4                   | +---------------------+ 1 row in set (0.00 sec)As we can observe from the above result set that when we will provide ... Read More

Both the functions are string functions and return the number of characters present in the string. But they differ in the concept that CHAR_LENGTH() function measures the string length in ‘characters’ whereas LENGTH() function measures the string length in ‘bytes’. In other words, we can say that CHAR_LENGTH() function is multi-byte safe i.e. it ignores whether the characters are single-byte or multi-byte. For example, if a string contains four 2-bytes characters then LENGTH().The function will return 8, whereas CHAR_LENGTH() function will return 4. In this sense, we can say that CHAR_LENGTH() gives precise result than LENGTH() function.The difference is especially ... Read More