In MySQL, the cursor uses and reads text data, and the mysql cursor reads text

In MySQL, the cursor uses and reads text data, and the mysql cursor reads text
Preface

I have never been familiar with database learning before. I only took a course on basic database knowledge in my undergraduate course. At that time, I was only interested in C ++. I naively thought that other classes were useless, and the database was not knowledgeable. I took the test in random. Now I am studying big data analysis and getting started with data mining, and I feel that the database cannot be crossed. It is not until now that I feel the importance of the courses such as operating system, compilation principles, and computer composition principles. In the face of vast knowledge, individuals are very small. Once you have mastered one idea, everything is not difficult. What is difficult is whether you are really calm down and take a look at the help documentation and carefully Google. Calm, calm, earnest, and persistent.

Cursor-cursor

After studying MySQL for a few days, I will talk about my understanding of cursors.

A cursor is similar to a pointer in C ++ and is used to point to the query result. For example, the data format after your query is as follows:

+------------+----------------------+------+------+------+-------------+----------+----------+------+---------+| station_id | get_time             | PM25 | PM10 | NO2  | temperature | pressure | humidity | wind | weather |+------------+----------------------+------+------+------+-------------+----------+----------+------+---------+| 001001     | 2/8/2013 9:00:00 PM  |  149 |   59 |   16 |          -5 |     1031 |       46 |    4 |       1 || 001001     | 2/8/2013 10:00:00 PM |  159 |   65 |   22 |          -5 |     1030 |       46 |    1 |       1 || 001001     | 2/9/2013 12:00:00 AM |  179 |   73 |   28 |          -6 |     1029 |       46 |    4 |       1 || 001001     | 2/9/2013 2:00:00 AM  |  194 |   73 |   29 |          -7 |     1028 |       49 |    3 |       1 || 001001     | 2/9/2013 3:00:00 AM  |  191 |   73 |   27 |          -7 |     1028 |       50 |    2 |       1 || 001001     | 2/9/2013 4:00:00 AM  |  194 |   73 |   25 |          -7 |     1026 |       53 |    2 |       1 || 001001     | 2/9/2013 5:00:00 AM  |  193 |   73 |   23 |          -7 |     1026 |       54 |    2 |       1 || 001001     | 2/9/2013 6:00:00 AM  |  192 |   73 |   21 |          -8 |     1026 |       52 |    2 |       1 || 001001     | 2/9/2013 7:00:00 AM  |  192 |   73 |   23 |          -8 |     1025 |       54 |    3 |       1 || 001001     | 2/9/2013 8:00:00 AM  |  190 |   73 |   20 |          -8 |     1025 |       55 |    3 |       1 |+------------+----------------------+------+------+------+-------------+----------+----------+------+---------+

If you want to process data one by one, you must use a cursor for loop processing.

The loaded data is in the varchar format, but for the second property "get_time", we need the format "datatime". We need to obtain the property value and perform cyclic processing.

To use a cursor, follow these steps:

1. Define the declare cursor name cursor for select statement

2. Define the variable declare continue handler for not found set variable name = true;

3. open cursor name

4. determine whether or not to end. If not, process the current cursor pointing to the value. If it ends, end the loop.

5. close the cursor name

Note: A cursor is generally called in a stored procedure (procedure). procedure is similar to a function in C ++, which encapsulates SQL statements, CALL 'Procedure _ name' directly. If a variable is used in a cursor (cursor), you must define the variable before declaring the cursor. The detailed code design is as follows:

Create definer = 'root' @ 'localhost' PROCEDURE 'strtodate' () begin -- defines a temporary variable used to store the converted time format declare temp datetime; -- defines a string temporary variable, declare str varchar (150); -- declare flag int default false; -- defines the cursor declare getTimeCursor cursor for select get_time from train; -- Define the end identifier declare continue handler for not found set flag = true; -- open the cursor open getTimeCursor; -- start loop processing read_loop: loop -- put the current cursor content in the variable fetch getTimeCursor into str; -- if the end mark is TRUE, the end loop if flag thenleave read_loop; end if; -- otherwise, each attribute is processed cyclically, call the string conversion date function set temp = (select str_to_date (str, '% c/% e/% Y % l: % I: % s % p ')); -- store the conversion result to the new table insert into time_test values (temp); -- end loop; -- close the cursor close getTimeCursor; -- Query Result select * from time_test limit 10; end

The str_to_date () function converts data of the string type to the date type. The query result is:

+---------------------+| get_time            |+---------------------+| 2013-02-09 16:00:00 || 2013-02-08 21:00:00 || 2013-02-08 22:00:00 || 2013-02-09 00:00:00 || 2013-02-09 02:00:00 || 2013-02-09 03:00:00 || 2013-02-09 04:00:00 || 2013-02-09 05:00:00 || 2013-02-09 06:00:00 || 2013-02-09 07:00:00 |+---------------------+

See, all strings are converted to the standard time format.

MySQL load data control

In fact, the above problem can be completely completed using another method, that is, to control the format when loading data. The specific SQL code is as follows:

use train;drop table traindata;create table if not exists traindata(id int auto_increment primary key,station_id varchar(10),get_time datetime,PM25 int,PM10 int,NO2 int,temperature int,pressure int,humidity int,wind double,weather int);load data localinfile 'f:\\dataset\\beijing\\crawleddata.txt'into table traindatafields terminated by ','(station_id, @var_time, PM25, PM10, NO2, temperature, pressure, humidity, wind, weather)set get_time = str_to_date(@var_time, '%c/%e/%Y %l:%i:%s %p');select * from traindata limit 10;

After being loaded into the database, the specific data format is as follows:

+----+------------+---------------------+------+------+------+-------------+----------+----------+------+---------+| id | station_id | get_time            | PM25 | PM10 | NO2  | temperature | pressure | humidity | wind | weather |+----+------------+---------------------+------+------+------+-------------+----------+----------+------+---------+|  1 |       1001 | 2013-02-08 21:00:00 |  149 |   59 |   16 |          -5 |     1031 |       46 |    4 |       1 ||  2 |       1001 | 2013-02-08 22:00:00 |  159 |   65 |   22 |          -5 |     1030 |       46 |    1 |       1 ||  3 |       1001 | 2013-02-09 00:00:00 |  179 |   73 |   28 |          -6 |     1029 |       46 |    4 |       1 ||  4 |       1001 | 2013-02-09 02:00:00 |  194 |   73 |   29 |          -7 |     1028 |       49 |    3 |       1 ||  5 |       1001 | 2013-02-09 03:00:00 |  191 |   73 |   27 |          -7 |     1028 |       50 |    2 |       1 ||  6 |       1001 | 2013-02-09 04:00:00 |  194 |   73 |   25 |          -7 |     1026 |       53 |    2 |       1 ||  7 |       1001 | 2013-02-09 05:00:00 |  193 |   73 |   23 |          -7 |     1026 |       54 |    2 |       1 ||  8 |       1001 | 2013-02-09 06:00:00 |  192 |   73 |   21 |          -8 |     1026 |       52 |    2 |       1 ||  9 |       1001 | 2013-02-09 07:00:00 |  192 |   73 |   23 |          -8 |     1025 |       54 |    3 |       1 || 10 |       1001 | 2013-02-09 08:00:00 |  190 |   73 |   20 |          -8 |     1025 |       55 |    3 |       1 |+----+------------+---------------------+------+------+------+-------------+----------+----------+------+---------+

See, standard data format.

 

Original content. For more information, see the source. Http://www.cnblogs.com/chuantingSDU/p/4243990.html

Contact: chuanting.zhang@gmail.com