Shulou(Shulou.com)05/31 Report--

This article mainly introduces examples of the usage of the mysql function, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

Testing the performance of Mysql can be done by

Select

Benchmark (100000Band SQL statement)

To check the time it takes for mysql to run the 100000 sql statement.

You can pass through

SELECT

*

FROM

Table name

PROCEDURE

ANALYSE

(

)

To analyze whether the size of each subsegment type is set properly

Tutorial of date addition and subtraction functions and examples commonly used in mysql

MySQL date type

MySQL date type: date format, storage space occupied, date range comparison.

Date Typ

Storage space

Date format

Date range

-

--

-

Datetime

8 bytes YYYY-MM-DD

HH:MM:SS 1000-01-01 00:00:00 ~

9999-12-31 23:59:59

Timestamp

4 bytes YYYY-MM-DD

HH:MM:SS 1970-01-01 00:00:01 ~

2038

Date

3 bytes

YYYY-MM-DD

1000-01-01

~ 9999-12-31

Year

1 bytes

YYYY

1901

~ 2155

When you create a table in MySQL, you can easily choose the right data type against the above table. But whether to choose datetime or

Timestamp, it might be a little difficult. These two date-time types have their own advantages: datetime has a large date range; timestamp

The storage space is relatively small, which is only half that of datetime.

In addition, columns of type timestamp have a feature: by default, when insert update data, timestamp

The column is automatically populated / updated with the current time (CURRENT_TIMESTAMP). "automatic" means that if you leave it alone, MySQL will handle it for you.

In general, I prefer to use the datetime date type.

MySQL time type: time format, storage space occupied, time range.

Time type

Storage space

Time format

Time range

-

--

-

Time

3 bytes

HH:MM:SS

-838PUR 59RU 59

~ 838PUR 59RU 59

It is a bit strange that the time range of time should have such a large range, especially when time can be negative. Later, I watched MySQL.

The manual knew that it was designed to meet the subtraction of two dates and times.

Select timediff ('2000 rigor 01, 31 23, 59, 59, 01)

00001);-- 743 virtual 59)

Select timediff ('2000 rigor 01 00 rig 00),' 2 000 rig 01 0 1, 31

23 / 59 / 59);-743 / 59 / 59 / 59

Select timediff ('23RV 59RU 59'

'12VOG 00VOG 00')

-- 11:59:59

Note that the two parameters of timediff can only be datetime/timestamp and time

Type, and the two parameter types should be the same. Namely: datetime/timestamp and datetime/timestamp comparison; time

Compared to time.

Although MySQL

There are a variety of date-time types in, but unfortunately, currently (2008-08-08) these date-time types can only be supported to the second level, not milliseconds and microseconds. There is no function that produces milliseconds.

"MySQL:MySQL date data type, MySQL time type usage summary" applies to MySQL version 5.X and above.

MySQL gets the current date-time function.

1.1 get the current date + time (date + time) function: now ()

Mysql > select now ()

+-+

| | now () |

+-+

| | 2008-08-08 22:20:46 |

+-+

In addition to the now () function that gets the current date and time, there are the following functions in MySQL:

Current_timestamp ()

, current_timestamp

, localtime ()

, localtime

, localtimestamp-(v4.0.6)

, localtimestamp ()-- (v4.0.6)

These date-time functions are all equivalent to now (). Since the now () function is short and easy to remember, it is recommended that you always use now ()

To replace the functions listed above.

1.2 get the current date + time (date + time) function: sysdate ()

The sysdate () date-time function is similar to now (), except that now () gets its value at the beginning of execution, and sysdate ()

Get the value dynamically when the function is executed. Take a look at the following example:

Mysql > select now (), sleep (3), now ()

+-+

| | now () | sleep (3) | now () | |

+-+

| | 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 | |

+-+

Mysql > select sysdate (), sleep (3), sysdate ()

+-+

| | sysdate () | sleep (3) | sysdate () | |

+-+

| | 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 | |

+-+

You can see that although the midway sleep is 3 seconds, the time value of the now () function is the same twice, and the time value obtained by the sysdate () function is different.

Three seconds. This is how sysdate () is described in MySQL Manual: Return the time at which the

Function executes .

The sysdate () date-time function is rarely used in general.

two。 Get current date (date) function: curdate ()

Mysql > select curdate ()

+-+

| | curdate () |

+-+

| | 2008-08-08 | |

+-+

The following two date functions are equivalent to curdate ():

Current_date ()

, current_date

3. Get current time (time) function: curtime ()

Mysql > select curtime ()

+-+

| | curtime () |

+-+

| | 22:41:30 |

+-+

The following two time functions are equivalent to curtime ():

Current_time ()

, current_time

4. Get the current UTC date and time functions: utc_date (), utc_time (), utc_timestamp ()

Mysql > select utc_timestamp (), utc_date ()

Utc_time (), now ()

+-+

| | utc_timestamp () | utc_date () | utc_time () | now () |

+-+

| | 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 |

| |

+-+

Because our country is located in the east eight time zone, so the local time = UTC time + 8 hours. UTC

Time is very useful when business involves multiple countries and regions.

Second, MySQL date and time Extract (select) function.

1. Select each part of the date time: date, time, year, quarter, month, day, hour, minute, second, microsecond

Set @ dt = '2008-09-100 07 purl 1530.123456'

Select date (@ dt);-- 2008-09-10

Select time (@ dt);-- 07Viru 1530.123456

Select year (@ dt);-- 2008

Select quarter (@ dt);-- 3

Select month (@ dt);-- 9

Select week (@ dt);-- 36

Select day (@ dt);-- 10

Select hour (@ dt);-- 7

Select minute (@ dt);-- 15

Select second (@ dt);-- 30

Select microsecond (@ dt);-- 123456

2. MySQL Extract () function, which can achieve similar functions as above:

Set @ dt = '2008-09-100 07 purl 1530.123456'

Select extract (year from @ dt);-- 2008

Select extract (quarter from @ dt);-- 3

Select extract (month from @ dt);-- 9

Select extract (week from @ dt);-- 36

Select extract (day from @ dt);-- 10

Select extract (hour from @ dt);-7

Select extract (minute from @ dt);-- 15

Select extract (second from @ dt);-- 30

Select extract (microsecond from @ dt);-- 123456

Select extract (year_month from @ dt);-- 200809

Select extract (day_hour from @ dt);-- 1007

Select extract (day_minute from @ dt);-- 100715

Select extract (day_second from @ dt);-- 10071530

Select extract (day_microsecond from @ dt);-10071530123456

Select extract (hour_minute from @ dt);

Select extract (hour_second from @ dt);-- 71530

Select extract (hour_microsecond from @ dt);-- 71530123456

Select extract (minute_second from @ dt);-- 1530

Select extract (minute_microsecond from @ dt);-- 1530123456

Select extract (second_microsecond from @ dt);-- 30123456

Except for the MySQL Extract () function without date (), time ()

In addition to the functions, other functions should be fully equipped. It also has the function of selecting 'day_microsecond'' and so on. Note that it is not just day and

Microsecond, but from the day part of the date to the microsecond part. How tough it is!

The only downside of the MySQL Extract () function is that you need to type the keyboard a few more times.

3. MySQL dayof... Functions: dayofweek (), dayofmonth (), dayofyear ()

Returns the position of the date parameter in a week, a month, or a year, respectively.

Set @ dt = '2008-08-08'

Select dayofweek (@ dt);-- 6

Select dayofmonth (@ dt);-- 8

Select dayofyear (@ dt);--

The date '2008-08-08' is the sixth day of the week (1 = Sunday, 2 = Monday,..., 7 =

Saturday); the 8th day of the month; the 221st day of the year.

4. MySQL week... Functions: week (), weekofyear (), dayofweek (), weekday ()

Yearweek ()

Set @ dt = '2008-08-08'

Select week (@ dt);-- 31

Select week (@ dt,3);-- 32

Select weekofyear (@ dt);-- 32

Select dayofweek (@ dt);-- 6

Select weekday (@ dt);-- 4

Select yearweek (@ dt);-- 200831

The MySQL week () function, which can have two parameters, can be found in the manual. Weekofyear () and week ()

Similarly, it is calculated that "one day" is located in the week of the year. Weekofyear (@ dt) is equivalent to week (@ dt,3).

The MySQL weekday () function, like dayofweek (), returns the position of "someday" in the week. The difference lies in the standard of reference

Weekday: (0 = Monday, 1 = Tuesday,..., 6 = Sunday); dayofweek: (1 =

Sunday, 2 = Monday,..., 7 = Saturday)

The MySQL yearweek () function, which returns year (2008) + week position (31).

5. MySQL returns the week and month name functions: dayname (), monthname ()

Set @ dt = '2008-08-08'

Select dayname (@ dt);-- Friday

Select monthname (@ dt);-- August

Think about it, how to return the Chinese name?

6. The MySQL last_day () function returns the last day of the month.

Select last_day (2008-02-01);-- 2008-02-29

Select last_day (2008-08-08);-- 2008-08-31

The MySQL last_day () function is very useful, for example, I want to know how many days there are in the current month, which can be calculated like this:

Mysql > select now (), day (last_day (now () as

Days

+-+ +

| | now () | days | |

+-+ +

| | 2008-08-09 11:45:45 | 31 | |

+-+ +

Third, MySQL date and time calculation function

1. MySQL adds a time interval to the date: date_add ()

Set @ dt = now ()

Select date_add (@ dt, interval 1 day);-- add 1 day

Select date_add (@ dt, interval 1 hour);-- add 1 hour

Select date_add (@ dt, interval 1 minute); -...

Select date_add (@ dt, interval 1 second)

Select date_add (@ dt, interval 1 microsecond)

Select date_add (@ dt, interval 1 week)

Select date_add (@ dt, interval 1 month)

Select date_add (@ dt, interval 1 quarter)

Select date_add (@ dt, interval 1 year)

Select date_add (@ dt, interval-1 day);-- sub 1 day

The MySQL adddate (), addtime () function can be replaced by date_add (). Here is date_add ()

Example of implementing the addtime () function:

Mysql > set @ dt = '2008-08-09 12purl 1233'

Mysql >

Mysql > select date_add (@ dt, interval '01purl 15purl 30'

Hour_second)

+-- +

| | date_add (@ dt, interval'01, interval'01, 15 hour_second) | |

+-- +

| | 2008-08-09 13:28:03 |

+-- +

Mysql > select date_add (@ dt, interval'1 01purl 15purl 30'

Day_second)

+-- +

| | date_add (@ dt, interval'1 01v 1515 day_second) |

+-- +

| | 2008-08-10 13:28:03 |

+-- +

Date_add () function, added "1 hour 15 minutes 30 seconds" and "1 hour 15 minutes a day" to @ dt, respectively.

Suggestion: always use the date_add () date-time function instead of adddate (), addtime ().

2. MySQL subtracts a time interval from the date: date_sub ()

Mysql > select date_sub ('1998-01-01 00 purse 0000'

Interval'1 1V 1V 1V 1L 'day_second)

+-- +

| | date_sub ('1998-01-01 00 day_second, interval' 1) |

| |

+-- +

| | 1997-12-30 22:58:59 |

+-- +

The MySQL date_sub () date-time function is the same as date_add (), so I won't repeat it. In addition, there are two functions in MySQL

Subdate (), subtime (), it is recommended that date_sub () be used instead.

3. MySQL alternative date function: period_add (PMagne N), period_diff (P1MaginP2)

The function parameter "P" is formatted as "YYYYMM" or "YYMM", and the second parameter "N" adds or subtracts N month (month).

MySQL period_add (N): date plus / minus N month.

Mysql > select period_add (200808 Magi 2)

Period_add (20080808 Ling Murray 2)

+-+ +

| | period_add (200808jue 2) | period_add (20080808mai2) | |

+-+ +

| | 200810 | 20080806 | |

+-+ +

MySQL period_diff (P1Magol P2): date P1-P2, return N months.

Mysql > select period_diff (200808, 200801)

+-+

| | period_diff (200808, 200801) | |

+-+

| | 7 |

+-+

In MySQL, these two date functions are rarely used.

4. MySQL date and time subtraction function: datediff (date1,date2)

Timediff (time1,time2)

MySQL datediff (date1,date2): subtracts date1-date2 from two dates to return the number of days.

Select datediff ('2008-08-08,' 2008-08-01);-- 7

Select datediff ('2008-08-01,' 2008-08-08);-7

MySQL timediff (time1,time2): subtracts two dates from time1-time2, and returns time

The difference.

Select timediff ('2008-08-08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08);--

08:08:08

Select timediff ('08rig 08 08mm,' 001R 00R 00');-- 08:08:08

Note: the two argument types of the timediff (time1,time2) function must be the same.

4. MySQL date conversion function and time conversion function

1. MySQL (time, second) conversion function: time_to_sec (time), sec_to_time (seconds)

Select time_to_sec ('01VO 05');-- 3605

Select sec_to_time (3605);-- '01VRO 05'

2. MySQL (date, days) conversion function: to_days (date), from_days (days)

Select to_days ('0000-00');-- 0

Select to_days ('2008-08-08');-733627

Select from_days (0);-'0000-00-00'

Select from_days (733627);-'2008-08-08'

3. MySQL Str to Date (string conversion to date) function: str_to_date (str, format)

Select str_to_date ('08Compact 09Accord 2008,'% m _ swap% dmax% Y');-- 2008-08-09

Select str_to_date ('08Compact 09Compact 08','% mAccord% dash% y');-- 2008-08-09

Select str_to_date ('08.09.2008,'% m.% d.% Y');-- 2008-08-09

Select str_to_date ('08 09 30 minutes,'% HVA% iRO% s');-- 08:09:30

Select str_to_date ('08.09.2008 08 select str_to_date 09,'% m.%d.%Y% HV% iRO% s');--

2008-08-09 08:09:30

As you can see, str_to_date (str,format)

Conversion function, you can convert some disorganized strings to date format. In addition, it can also be converted into time. "format" can be found in MySQL.

manual.

4. MySQL Date/Time to Str (date / time converted to string) function: date_format (date,format)

Time_format (time,format)

Mysql > select date_format ('2008-08-08 22 purge 2300mm,'% W

% M% Y')

+-- +

| | date_format ('2008-08-08 22 2300,'% W% M% Y') |

+-- +

| | Friday August 2008 |

+-- +

Mysql > select date_format ('2008-08-08 22 purl 23 purl 01'

'% Y% m% d% H% I% s')

+-- +

| | date_format ('2008-08-08 22 23lane 01mm,'% Y% m% d% H% I% s') |

+-- +

| | 20080808222301 | |

+-- +

Mysql > select time_format ('22 231'

'% H.I.% s')

+-+

| | time_format ('22 _ 22 _

+-+

| | 22.23.01 |

+-+

MySQL date and time conversion function: date_format (date,format)

Time_format (time,format) can convert a date / time into a variety of string formats. It is

An inverse transformation of the str_to_date (str,format) function.

5. MySQL obtains the time format function of country and region: get_format ()

MySQL get_format () syntax:

Get_format (date | time | datetime

'eur' |' usa' | 'jis' |' iso' | 'internal'

All examples of the use of MySQL get_format ():

Select get_format (date,'usa');--'% m.d.% Y'

Select get_format (date,'jis');--'% Ymuri% mmury% d'

Select get_format (date,'iso');--'% Ymuri% mmury% d'

Select get_format (date,'eur');--'% d.m.% Y'

Select get_format (date,'internal');--'% Y% m% d'

Select get_format (datetime,'usa');--'% Y-%m-%d% H.% I.% s'

Select get_format (datetime,'jis');--'% Y-%m-%d% HRV% iRU% s'

Select get_format (datetime,'iso');--'% Y-%m-%d% HRV% iRU% s'

Select get_format (datetime,'eur');--'% Y-%m-%d% H.% I.% s'

Select get_format (datetime,'internal');--'% Y% m% d% H% I% s'

Select get_format (time,'usa'); -'% h:%i:%s% p'

Select get_format (time,'jis');--'% HRV% iRO% s'

Select get_format (time,'iso');--'% HRV% iRO% s'

Select get_format (time,'eur');--'% H.I.% s'

Select get_format (time,'internal');--'% H% I% s'

The MySQL get_format () function uses fewer opportunities in practice.

6. MySQL patchwork date and time function: makdedate (year,dayofyear)

Maketime (hour,minute,second)

Select makedate (2001-31);-- '2001-01-31'

Select makedate (2001-32);-- '2001-02-01'

Select maketime (12715-30);--'12-15-30'

5. MySQL timestamp (Timestamp) function

1. MySQL gets the current timestamp function: current_timestamp, current_timestamp ()

Mysql > select current_timestamp

Current_timestamp ()

+-+ +

| | current_timestamp | current_timestamp () |

+-+ +

| | 2008-08-09 23:22:24 | 2008-08-09 23:22:24 | |

+-+ +

2. MySQL (Unix timestamp, date) conversion function:

Unix_timestamp ()

Unix_timestamp (date)

From_unixtime (unix_timestamp)

From_unixtime (unix_timestamp,format)

Here is an example:

Select unix_timestamp ();-- 1218290027

Select unix_timestamp ('2008-08-08');-1218124800

Select unix_timestamp ('2008-08-08 / 12);-- 1218169800

Select from_unixtime (1218290027);-- '2008-08-09 21 purl 53 purl 47'

Select from_unixtime (1218124800);-- '2008-08-08 00001

Select from_unixtime (1218169800);-- '2008-08-08 12 purl 3015'

Select from_unixtime (1218169800,'% Y% D% M% h:%i:%s% x');

'2008 8th August 12:30:00 2008'

3. MySQL timestamp (timestamp) conversion, increment and subtraction of functions:

Timestamp (date)-date to timestamp

Timestamp (dt,time)-- dt + time

Timestampadd (unit,interval,datetime_expr)-

Timestampdiff (unit,datetime_expr1,datetime_expr2)-

Take a look at the example section:

Select timestamp ('2008-08-08');-- 2008-08-08 00:00:00

Select timestamp ('2008-08-08 / 08 / 08 / 08 / 08 / 08 / 01 / 01 / 01 / 08 / 08 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01);-- 2008-08-08

09:01:01

Select timestamp ('2008-08-08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08)

2008-08-18 09:01:01

Select timestampadd (day, 1, '2008-08-08-08);

2008-08-09 08:00:00

Select date_add ('2008-08-08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08

2008-08-09 08:00:00

The MySQL timestampadd () function is similar to date_add ().

Select timestampdiff (year,'2002-05-01);-1

Select timestampdiff (day, 2002-05-01)

Select timestampdiff (hour,'2008-08-08 12)

0012);-- 12

Select datediff ('2008-08-08 12-0-0-0-0, 2008-08-01-00-0-0-0-0-01-0-0))

-- 7

The MySQL timestampdiff () function is much more powerful than datediff (), datediff ()

Only the number of days between two dates (date) can be calculated.

6. MySQL time zone (timezone) conversion function

Convert_tz (dt,from_tz,to_tz)

Select convert_tz ('2008-08-08 12-00-0-8-12-0-0-0-8-0-8-0-8-0-8-0-8-0-0-0-0-0');--

2008-08-08 04:00:00

Time zone conversion can also be achieved through date_add, date_sub, and timestampadd.

Select date_add ('2008-08-08 1200 hour, interval-8)

2008-08-08 04:00:00

Select date_sub ('2008-08-08 1200 hour, interval 8);

2008-08-08 04:00:00

Select TO_DAYS (str_to_date ('12 shock 2001 12:00:00 AM','%m/%d/%Y'))

-

TO_DAYS (str_to_date ('11pm) 28pm 12:00:00

AM','%m/%d/%Y')) as a from table1

Thank you for reading this article carefully. I hope the article "examples of mysql function usage" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.