search
HomeDatabaseMysql TutorialMySQL Basics Tutorial 11 - Cast Functions and Operators of Functions


  • BINARY

The BINARY operator throws the following string into a binary string. This is a simple way to force column comparisons to be done byte-by-byte rather than character-by-character. This makes the comparison case-sensitive, even if the column is not defined as a BINARY or BLOB. BINARY also produces trailing whitespace, 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 abbreviation of CAST (str AS BINARY).

Note that in some contexts, if you assign an indexed column to BINARY, MySQL will not be able to effectively use the index.

If you want to perform a case-sensitive comparison of a BLOB value or other binary string, you can do this by taking advantage of the fact that binary strings do not have character sets, so there are no folders. concept. To perform a case-sensitive comparison, 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 its latin1 name in the above statement.

CONVERT() can generally be used to compare strings that appear in different character sets.

  • CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)

## The CAST() and CONVERT() functions can be used to obtain a value of one type and generate another type of value.

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. See the BINARY operator entry in this chapter for a description of how it affects the comparison result.
If a random length

N

is given, BINARY[

N] causes the cast to use this parameter for no more than N bytes . Likewise, CHAR[N] will cause the cast to use no more than N characters. CAST() and CONVERT(... USING...) are standard SQL syntax. The non-USING format of CONVERT() is ofis ODBC syntax.

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

SELECT CONVERT('abc' USING utf8);

When you want The cast function is useful when creating a column of a special type 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 lexical order. Normally sorting of ENUM columns occurs when using internal numeric values. Dispatch these values ​​​​in lexical order to the CHAR result:

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 used as part of a more complex expression such as CONCAT('Date: ',CAST(NOW() AS DATE)), CAST() will also change 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. See "Date and Time Functions".

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

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

If you 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 operations on 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. This can be overridden using the SIGNED and UNSIGNED cast operators. Dispatches operations to signed or unsigned 64-bit integers respectively.

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

Note that if any of the operands is a floating point value, the result will be a floating point value and will not be affected by the above rules (for this point, DECIMAL column values ​​are treated as floating point values) .

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

若你在一个算术运算中使用了一个字符串,它会被转化为一个浮点数。

 以上就是MySQL基础教程11 —— 函数之Cast函数和操作符的内容,更多相关内容请关注PHP中文网(www.php.cn)!


Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are stored procedures in MySQL?What are stored procedures in MySQL?May 01, 2025 am 12:27 AM

Stored procedures are precompiled SQL statements in MySQL for improving performance and simplifying complex operations. 1. Improve performance: After the first compilation, subsequent calls do not need to be recompiled. 2. Improve security: Restrict data table access through permission control. 3. Simplify complex operations: combine multiple SQL statements to simplify application layer logic.

How does query caching work in MySQL?How does query caching work in MySQL?May 01, 2025 am 12:26 AM

The working principle of MySQL query cache is to store the results of SELECT query, and when the same query is executed again, the cached results are directly returned. 1) Query cache improves database reading performance and finds cached results through hash values. 2) Simple configuration, set query_cache_type and query_cache_size in MySQL configuration file. 3) Use the SQL_NO_CACHE keyword to disable the cache of specific queries. 4) In high-frequency update environments, query cache may cause performance bottlenecks and needs to be optimized for use through monitoring and adjustment of parameters.

What are the advantages of using MySQL over other relational databases?What are the advantages of using MySQL over other relational databases?May 01, 2025 am 12:18 AM

The reasons why MySQL is widely used in various projects include: 1. High performance and scalability, supporting multiple storage engines; 2. Easy to use and maintain, simple configuration and rich tools; 3. Rich ecosystem, attracting a large number of community and third-party tool support; 4. Cross-platform support, suitable for multiple operating systems.

How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.