Provides you with an in-depth understanding of the MYSQL Cast function.

The MYSQL Cast function is a very important MYSQL function. The following describes the syntax and usage of the MYSQL Cast function in detail, hoping that you can have a better understanding of the MYSQL Cast function.

BINARY
The BINARY operator returns a BINARY string. This is a simple way to promote column comparison by byte rather than by character. This makes the comparison case sensitive, even if the column is not defined as BINARY or BLOB. BINARY will also generate trailing white space, making it more conspicuous.

 
 

  
  
mysql>   SELECT   'a'   =   'A';     
  
  
      
  
  
                  ->   1     
  
  
      
  
  
mysql>   SELECT   BINARY   'a'   =   'A';     
  
  
                   ->   0     
  
  
mysql>   SELECT   'a'   =   'a   ';     
  
  
                   ->   1     
  
  
mysql>   SELECT   BINARY   'a'   =   'a   ';     
  
  
                   ->   0    
 
 

BINARY affects the entire comparison; it can be given before any operand and produce the same result.

BINARY str is the scaling form of CAST (str as binary.

Note: In some contexts, if you send an indexed column to BINARY, MySQL will not be able to use this index effectively.
If you want to compare the case sensitivity of a BLOB value or other binary strings, you can use the fact that the binary string does not have a character set to achieve this purpose, so that there is no concept of a folder. To perform a case-sensitive comparison, you can use the CONVERT () function to CONVERT a string value to a case-insensitive character set. The result is a non-binary string, so the LIKE operation is not case sensitive:

 
 

  
  
SELECT   'A'   LIKE   CONVERT(blob_col   USING   latin1)   FROM   tbl_name;     
 
 

To use a different character set, replace latin1 in the preceding statement.

CONVERT () is generally used to compare strings that appear in different character sets.
CAST (expr AS type), CONVERT (expr, type), CONVERT (expr USING transcoding_name)
CAST () and CONVERT () functions can be used to obtain values of one type and generate values of another type.

This type can be one of the following values:
BINARY [(N)]
CHAR [(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
BINARY generates a BINARY string.

If a random length of N is given, BINARY [N] causes cast to use this parameter no more than N Bytes. Similarly, CHAR [N] causes cast to use this parameter for no more than N characters.

 
 

  
  
CAST () and CONVERT (... USING...) is a standard SQL syntax. The non-USING format of CONVERT () is the ofis ODBC syntax.
 
 

CONVERT () with USING is used to CONVERT data between different character sets. In MySQL, the automatic decoding name is the same as the corresponding Character Set Name. For example. This statement converts the 'abc' character set in the server's default character set to the corresponding character string in the utf8 character set:

 
 

  
  
SELECT   CONVERT('abc'   USING   utf8);    
 
 

The cast function is useful when you want to CREATE a special type column in a CREATE... SELECT statement:

 
 

  
  
CREATE   TABLE   new_table   SELECT   CAST('2000-01-01'   AS   DATE);    
 
 

This function is also used to sort ENUM columns in the lexical order. Generally, sorting of ENUM columns occurs when internal values are used. Send these values to the CHAR result in the lexical order:

 
 

  
  
SELECT   enum_col   FROM   tbl_name   ORDER   BY   CAST(enum_col   AS   CHAR);    
 
 

CAST (str as binary) is the same as binary str. CAST (expr as char) treats the expression AS a string with the default character set.

If a part of a complex expression such as concat ('date: ', CAST (NOW () AS Date) is used, CAST () also changes the result.

You should not use CAST () to EXTRACT data in different formats, but you can use string functions such as LEFT () or EXTRACT () instead.

To assign a string to a value in a numeric context, you do not need to do anything except to use the string value as a number:

 
 

  
  
mysql>   SELECT   1+'1';     
  
  
                ->   2    
 
 

To use a number in a string context, the number is automatically converted to a BINARY string.

 
 

  
  
mysql>   SELECT   CONCAT('hello   you   ',2);     
  
  
                  ->   'hello   you   2'    
 
 

MySQL supports the operation of signed and unsigned 64-bit values. If you are using a numeric operator (such as +) and one of the operands is an unsigned integer, the result is unsigned. You can use the SIGNED and UNSIGNED cast operators to overwrite it. Assign the operation to a signed or unsigned 64-bit integer.

 
 

  
  
mysql>   SELECT   CAST(1-2   AS   UNSIGNED)     
  
  
                  ->   18446744073709551615     
  
  
mysql>   SELECT   CAST(CAST(1-2   AS   UNSIGNED)   AS   SIGNED);     
  
  
                  ->   -1    
 
 

Note that if any operand is a floating point value, the result is a floating point value and is not affected by the above rules (for this purpose, the DECIMAL column value is considered as a floating point value ).

 
 

  
  
mysql>   SELECT   CAST(1   AS   UNSIGNED)   -   2.0;     
  
  
                  ->   -1.0     
  
  
     
 
 

If you use a string in an arithmetic operation, it is converted into a floating point number.

Learn more about mysql CONCAT () Functions

How to view the three MySQL character sets

View MySQL Default Character Set

How MySQL defines foreign keys

MySql stored procedure with Parameters