- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 6702 Articles for Database
![Moumita](https://www.tutorialspoint.com/assets/profiles/13507/profile/60_122499-1557898491.jpg)
142 Views
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
![Sreemaha](https://www.tutorialspoint.com/assets/profiles/13570/profile/60_64977-1512709068.jpg)
332 Views
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)
![varma](https://www.tutorialspoint.com/assets/profiles/13568/profile/60_26300-1512638592.jpg)
266 Views
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
![Smita Kapse](https://www.tutorialspoint.com/assets/profiles/13597/profile/60_103706-1512718957.jpg)
61 Views
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)
![Swarali Sree](https://www.tutorialspoint.com/assets/profiles/13515/profile/60_84115-1512649510.jpg)
59 Views
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
![Anvi Jain](https://www.tutorialspoint.com/assets/profiles/13591/profile/60_98631-1512716973.jpg)
194 Views
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)
![Sai Nath](https://www.tutorialspoint.com/assets/profiles/13525/profile/60_63072-1512539005.jpg)
198 Views
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
![Arushi](https://www.tutorialspoint.com/assets/profiles/13510/profile/60_55588-1512537391.jpg)
2K+ Views
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
![Sai Subramanyam](https://www.tutorialspoint.com/assets/profiles/13512/profile/60_68191-1512710774.jpg)
270 Views
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)