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

In MySQL, BIN() function is used to get the corresponding string representation of the binary value of a number. It considers the number as a DECIMAL number. Syntax BIN(value) Here value, a BIGINT number, is the number whose binary value is to be retrieved. Example mysql> Select BIN(15); +---------+ | BIN(15) | +---------+ | 1111 | +---------+ 1 row in set (0.00 sec)

As we know that MySQL will produce an error if overflow occurs during the assessment of numeric expressions. For example, the largest signed BIGNT is 9223372036854775807, so the following expression will produce an error −mysql> Select 9223372036854775807 + 1; ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807+1)'MySQL can handle such kind of overflows in following ways:BY CONVERTING VALUE TO UNSIGNEDMySQL enables such kind of operations by converting the values to unsigned as follows −mysql> Select CAST(9223372036854775807 AS UNSIGNED) +1; +------------------------------------------+ | CAST(9223372036854775807 AS UNSIGNED) +1 | +------------------------------------------+ |                   ... Read More

Handling of MySQL numeric value that is out of allowed range of column data type depends upon the SQL mode in following ways −(A) Enabled SQL strict mode - When strict SQL mode is enabled, MySQL returns the error on entering the put-of-range value. In this case, the insertion of some or all the values got failed.For example, we have created a table with two columns having TINYINT and UNSIGNED TINYINT as their data types on columns.mysql> Create table counting(Range1 Tinyint, Range2 Tinyint Unsigned); Query OK, 0 rows affected (0.14 sec)Now with the help of the following command, we enabled the ... Read More

This can be understood with the help of an example in which NULL value has been inserted in an AUTO_INCREMENT column and MySQL deliver a new sequence number.mysql> Create table employeeinfo(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Name Varchar(10)); Query OK, 0 rows affected (0.16 sec) mysql> Insert into employeeinfo(id, Name) values(NULL, 'Saurabh'); Query OK, 1 row affected (0.07 sec) mysql> Select * from employeeinfo; +----+---------+ | id | Name    | +----+---------+ | 1  | Saurabh | +----+---------+ 1 row in set (0.00 sec)As we can observe from the above example that the column ‘id’ has been ... Read More

When we insert NULL value to AUTO_INCREMENT column, MySQL will return sequence number.Examplemysql> Create table employee(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Name Varchar(10)); Query OK, 0 rows affected (0.16 sec) mysql> Insert into employee(id, Name) values(NULL, 'Gaurav'); Query OK, 1 row affected (0.07 sec) mysql> Select * from employee; +----+---------+ | id | Name    | +----+---------+ | 1  | Gaurav  | +----+---------+ 1 row in set (0.00 sec)

In this case, it means we are providing an empty string as an argument to the CHAR_LENGTH() function. It will return 0 on providing empty string because there are no characters to be counted by CHAR_LENGTH() function.Examplemysql> Select CHAR_LENGTH(''); +-----------------+ | CHAR_LENGTH('') | +-----------------+ | 0               | +-----------------+ 1 row in set (0.00 sec)