当前位置：网站首页>Data type conversion in MySQL 8 | learning functions cast() and convert()

Data type conversion in MySQL 8 | learning functions cast() and convert()

2022-07-22 12:14:00 【Navicat China】

Whenever data is classified into different types , It is inevitable to convert from one data type to another . In my mind , A common use example is processing through query parameters or POST Request body from Web Variables passed in from the form . Serializing data for sending over the network tends to cast all variables into strings . therefore , They usually need to be converted to a more appropriate data type , For example, digital 、 Date or data type you have .

In a relational database , Reasons for converting one data type to another include migrating data from one database type to another 、 Change the data type of the column or temporarily switch between data types for calculation . stay MySQL in , We can use CAST() and CONVERT() Function to convert between data types . In today's article , We will learn how to use these two functions , And illustrate their usage through examples .

What's the difference ？

CAST() and CONVERT() Can be changed MySQL Data types in . Because the two are very similar , many SQL Novice （ And some more experienced users ！） Want to know the difference . The main difference is that CONVERT() You can also convert the character set of data into another character set .CAST() Cannot be used to change the character set . therefore , Unless you need to convert the character set ,CAST() It should be yours goto Conversion function .

CAST() function

MySQL CAST() Accept two inputs ：

Data to be typed
The data type you want to convert this data to （ Decimal system 、 Character etc. ）. You can convert data into BINARY、CHAR、DATE、DATETIME、TIME、DECIMAL、SIGNED、UNSIGNED data type .

H This is grammar ：

CAST(data as data_type)

Real examples

CAST() A useful application of functions is to make very large data types less cumbersome （ More practical ？）. The following query returns information about MySQL Sakila Information about specific movies in the sample database . One of its columns description Is a text field . This means that it can store a large amount of text ！ We can use CAST() Truncate the description to 100 Characters , So we won't get the whole book about movies ：

Speaking of Sakila Sample database , You know it takes MySQL Is the dolphin mascot named ？ It is from the user in “Name the Dolphin” Selected from a large number of names suggested in the competition . The name of the award is from Africa Eswatini（ front Swaziland） Open source software developers Ambrose Twebaze Submit .

CONVERT() function

CONVERT() The syntax of function and CAST() similar , But the format of expression and result type is slightly different . One way is to provide two separate parameters ：

CONVERT(expr, data_type)

besides ,data_type Parameters can be CAST() Any of the same types supported by the function .

Real examples

because CAST() and CONVERT() The main difference between them is that the latter can convert the character set of columns into different character sets , Let's actually demonstrate .

The first thing to notice is , The syntax of converting character sets is slightly different . under these circumstances , We need to add USING keyword ：

CONVERT(expr USING charset);

stay Navicat for MySQL（ or Navicat Premium） in , We can see the character set and sorting rules of the table in the information pane ：

Consider this , We can CONVERT() Function is applied to the previous query , take description Fields from UTF-8 Convert to Latin1. If you're curious , The difference between the two is , stay latin1 in , Each character is exactly one byte long , And in the utf8 in , A character can consist of multiple bytes . therefore utf8 Than latin1 There are more characters . Besides , The characters they share are not necessarily represented by the same bytes or byte sequences .

summary

In today's article , We saw how to use CAST() Convert data to different types , And how to use it CONVERT() Convert between character sets . To reiterate ,CAST() It should be yours goto Conversion function . and CONVERT() It is more suitable for switching between character sets . If you are right about Navicat for MySQL Interested in , Free trial 14 God ！