Home Database Mysql Tutorial MySQL中字符串函数详细介绍(1)

MySQL中字符串函数详细介绍(1)

Jun 07, 2016 pm 04:07 PM
mysql string introduce function string yes detailed

符串或串(String)是由零个或多个字符组成的有限序列。一般记为 s='a1a2an'(n=0)。它是编程语言中表示文本的数据类型。 通常以串的整体作为操作对象,如:在串中查找某个子串、求取一个子串、在串的某个位置上插入一个子串以及删除一个子串等。两个字符串相等

符串或串(String)是由零个或多个字符组成的有限序列。一般记为 s='a1a2•••an'(n>=0)。它是编程语言中表示文本的数据类型。

通常以串的整体作为操作对象,如:在串中查找某个子串、求取一个子串、在串的某个位置上插入一个子串以及删除一个子串等。两个字符串相等的充要条件是:长度相等,并且各个对应位置上的字符都相等。设p、q是两个串,求q在p中首次出现的位置的运算叫做模式匹配。串的两种最基本的存储方式是顺序存储方式和链接存储方式。

下面我们来看看MySQL中的字符串函数

假如结果的长度大于 max_allowed_packet 系统变量的最大值时,字符串值函数的返回值为NULL。

对于在字符串位置操作的函数,第一个位置的编号为 1。

◆ ASCII(str)

返回值为字符串str 的最左字符的数值。假如str为空字符串,则返回值为 0 。假如str 为NULL,则返回值为 NULL。 ASCII()用于带有从 0到255的数值的字符。

1

2

3

4

5

6

7

8

9

10

11

mysql> SELECT ASCII('2');

 

        -> 50

 

mysql> SELECT ASCII(2);

 

        -> 50

 

mysql> SELECT ASCII('dx');

 

        -> 100

Copy after login

见 ORD()函数。

◆ BIN(N)

返回值为N的二进制值的字符串表示,其中  N 为一个longlong (BIGINT) 数字。这等同于 CONV(N,10,2)。假如N 为NULL,则返回值为 NULL。

1

mysql> SELECT BIN(12);<br>-> '1100'

Copy after login

◆ BIT_LENGTH(str)

返回值为二进制的字符串str 长度。

1

mysql> SELECT BIT_LENGTH('text');<br>-> 32

Copy after login

◆ CHAR(N,... [USING charset])

CHAR()将每个参数N理解为一个整数,其返回值为一个包含这些整数的代码值所给出的字符的字符串。NULL值被省略。

1

mysql> SELECT CHAR(77,121,83,81,'76');<br>-> 'MySQL'<br>mysql> SELECT CHAR(77,77.3,'77.3');<br>-> 'MMM'

Copy after login

大于 255的CHAR()参数被转换为多结果字符。 例如,CHAR(256) 相当于 CHAR(1,0), 而CHAR(256*256) 则相当于 CHAR(1,0,0):

1

mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));<br>+----------------+----------------+<br>| HEX(CHAR(1,0)) | HEX(CHAR(256)) |<br>+----------------+----------------+<br>| 0100           | 0100           |<br>+----------------+----------------+<br>mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));<br>+------------------+--------------------+<br>| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |<br>+------------------+--------------------+<br>| 010000           | 010000             |<br>+------------------+--------------------+

Copy after login

CHAR()的返回值为一个二进制字符串。可选择使用USING语句产生一个给出的字符集中的字符串:

1

mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));<br>mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));<br>+---------------------+--------------------------------+<br>| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |<br>+---------------------+--------------------------------+<br>| binary              | utf8                           |<br>+---------------------+--------------------------------+

Copy after login

如果 USING已经产生,而结果字符串不符合给出的字符集,则会发出警告。同样,如果严格的SQL模式被激活,则CHAR()的结果会成为 NULL。

◆ CHAR_LENGTH(str)

返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

◆ CHARACTER_LENGTH(str)

CHARACTER_LENGTH()是CHAR_LENGTH()的同义词。

◆ COMPRESS(string_to_compress)

压缩一个字符串。这个函数要求 MySQL已经用一个诸如zlib的压缩库压缩过。   否则,返回值始终是NULL。UNCOMPRESS() 可将压缩过的字符串进行解压缩。

1

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));<br>-> 21<br>mysql> SELECT LENGTH(COMPRESS(''));<br>-> 0<br>mysql> SELECT LENGTH(COMPRESS('a'));<br>-> 13<br>mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));<br>-> 15

Copy after login

压缩后的字符串的内容按照以下方式存储:

空字符串按照空字符串存储。

非空字符串未压缩字符串的四字节长度进行存储(首先为低字节),后面是压缩字符串。如果字符串以空格结尾,就会在后加一个"."号,以防止当结果值是存储在CHAR或VARCHAR类型的字段列时,出现自动把结尾空格去掉的现象。(不推荐使用 CHAR 或VARCHAR 来存储压缩字符串。最好使用一个 BLOB 列代替)。

Statement of this Website
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

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

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

MySQL's Role: Databases in Web Applications MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

How to start mysql by docker How to start mysql by docker Apr 15, 2025 pm 12:09 PM

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

Laravel Introduction Example Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

Solve database connection problem: a practical case of using minii/db library Solve database connection problem: a practical case of using minii/db library Apr 18, 2025 am 07:09 AM

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

How to install mysql in centos7 How to install mysql in centos7 Apr 14, 2025 pm 08:30 PM

The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting

Centos install mysql Centos install mysql Apr 14, 2025 pm 08:09 PM

Installing MySQL on CentOS involves the following steps: Adding the appropriate MySQL yum source. Execute the yum install mysql-server command to install the MySQL server. Use the mysql_secure_installation command to make security settings, such as setting the root user password. Customize the MySQL configuration file as needed. Tune MySQL parameters and optimize databases for performance.

MySQL and phpMyAdmin: Core Features and Functions MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

Laravel framework installation method Laravel framework installation method Apr 18, 2025 pm 12:54 PM

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

See all articles