MySQL for datetime source analysis

http://tsecer.blog.163.com/blog/static/150181720160117355684/ a grammatical analysis of time comparisonIn MySQL, the usual time is an essential type, and a special place of this type is its comparison function. In fact, even if the comparison function of the string and the comparison to the int is completely different, but this difference does not want to save the time in datetime so visually shocking and performance is so obvious. Another problem here is that when MySQL YACC is parsing, this time the parser does not read the structure in a field, or does not fix the Fiedl, so when parser sees a field > " 2016-01-11 00:00:00 "In such a string, it is not known whether this place is going to be a string comparison or a time comparison, but it is necessary to fix each field of an SQL expression to find the type of the corresponding field, And then the type of judgment, here is the main explanation is a comparison function delay setting problem. Since MySQL has a built-in Mysql.event table that contains fields with DateTime types, it is more appropriate to use this built-in type for comparison: Select Sql_no_cache * from event where created > "2016-01-10 00:00:00"; in Sql_yacc.yy's parsing function, the comparison was created with different creator item:bool_pri:          BOOL _pri is Null_sym%prec is          {            $$= New (yythd-> Mem_root) Item_func_isnull ($);            if ($$ = = NULL)               mysql_yyabort;         }......        | BOOL_PRI comp_op predicate%prec eq      &NBsp   {            $$= (*$2) (0)->create ($1,$3);            if ($$ = = NULL)               mysql_yyabort;         }&NBSP;C omp_op:          EQ     {$$ = &comp_eq_creator;}         | GE     {$$ = &comp_ge_creator;}         | gt_sym {$$ = &comp_gt_creator;}         | LE     {$$ = &comp_le_creator;}         | LT     {$$ = &comp_lt_creator;}         | NE     {$$ = &comp_ne_creator;}         item_bool_func2* ge_creator::create (item *a, item *b) const{  return new Item_func_ge (A, b);} second, how to determine the specific comparison method Setup_conds-->>item_bool_func2::fix_length_and_dec-->>item_bool_func2::set_cmp_func-->>arg_ What comparator::set_cmp_func-->> can see is that, after entering the function, the comparison type of the DateTime type is prioritized, which is the highest priority, if the two sides of the comparison are of the DateTime type, If the other is a string type, the conversion to the string time is performed.  int Arg_comparator::set_cmp_func (Item_result_field *owner_arg,              & nbsp                         Item **A1, item **a2,    &NB Sp                                 M_result type) {  enum Enum_date_cmp_type cmp_type;  ulonglong const_value= (ulonglong) -1;  thd= Current_ thd;  owner= owner_arg;  set_null= set_null && owner_arg;  a= a1;  b= a2;  thd= current_t hd;   if ((cmp_type= can_compare_as_dates (*a, *b, &const_value))   {    a_type= (*a) Field_type ();    B_Type= (*b)->field_type ();    a_cache= 0;    b_cache= 0;     if (const_value! = (Ulon Glong)-1)     {     /*        cache_converted_constant can ' t be used here BEC Ause it can ' t        correctly convert a DATETIME value from string to int representation.  &nbsp ;   */      Item_cache_int *cache= new Item_cache_int (mysql_type_datetime);     /* Mark The cache as non-const to prevent re-caching. */      Cache->set_used_tables (1);      if (! ( *a)->is_datetime ()       {        Cache->store ((*a), Const_value);        a_cache= cache;        a= (Item * *) &a_cache;     }  &nbs P   else      {        Cache->store ((*b), Const_value);      &NBSP ; B_cache= cache;       b= (Item *) &b_cache;     }   }    is_nulls_eq= is_owner_equ Al_func ();    func= &Arg_comparator::compare_datetime;    get_value_a_func= &get_ datetime_value;    get_value_b_func= &get_datetime_value;    return 0; }  else if ( Type = = String_result && (*a)->field_type () = = Mysql_type_time &&          &NB SP; (*b)->field_type () = = Mysql_type_time)   {   /* Compare time values as integers. */    A_ca che= 0;    b_cache= 0;    is_nulls_eq= is_owner_equal_func ();    func= &arg_comparator ::compare_datetime;    get_value_a_func= &get_time_value;    get_value_b_func= &get_time _value;    return 0; }  else if (type = = String_result &&          & nbsp; (*a)->result_type () = = String_result &&            (*b)->result_type () = = String_result)   {    Dtcollation coll;    Coll.set ((*a)->collation.collation);    if (Agg_item_set_converter (Coll, owner-> Func_name (),                              &NBSP ; b, 1, My_coll_cmp_conv, 1)       return 1; }  else if (Try_year_cmp_func (type))     RE Turn 0;   a= cache_converted_constant (THD, A, &a_cache, type);  b= cache_converted_constant (THD, B, & Amp;b_cache, type);  return Set_compare_func (Owner_arg, type);} If the other end of the Datefield is not of type string, the comparison goes to another branch and is compared by the int type: void Item_bool_func2::fix_length_and_dec () ......  if (!thd-> Lex->is_ps_or_view_context_analysis ())   {    if (Args[0]->real_item ()->type () = = FIELD_ITEM)     {      Item_field *field_item= (item_field*) (Args[0]->real_item ()); &NBSp     if (Field_item->field->can_be_compared_as_longlong () &&         ! ( Field_item->is_datetime () &&            args[1]->result_type () = = String_ RESULT)       {        if (Convert_constant_item (THD, Field_item, &args[1]))         {          Cmp.set_cmp_func (this, Tmp_arg, tmp_arg+1,    &N Bsp                      int_result); Works for all types.          args[0]->cmp_context= args[1]->cmp_context= INT_RESULT;&N Bsp         return;       }     }   }  iii. parsing of datetime types Enum Enum_mysql_timestamp_typestr_to_datetime (const char *STR, UINT length, mysql_time *l_time,      & nbsp         UINT flags, int *was_cut) {......  for (i = start_loop;       i < Max_d Ate_parts-1 && str! = end &&         my_isdigit (&AMP;MY_CHARSET_LATIN1,*STR); nbsp      i++)   {......    while (str!! = End &&            (my_i Spunct (&AMP;MY_CHARSET_LATIN1,*STR) | |             My_isspace (&AMP;MY_CHARSET_LATIN1,*STR))     {      IF (My_isspace (&AMP;MY_CHARSET_LATIN1,*STR))       {        if (! ( Allow_space & (1 << i))         {          *was_cut= 1;  &nbsp ;       Dbug_return (mysql_timestamp_none);       }        FOUND_SPA Ce= 1;     }      str++;      found_delimitier= 1;                      /* should be a ' normal ' date */   } It can be seen here that the delimiter for each field is not the same as our common so-called "Yyyy-mm-dd HH:MM:SS", but an arbitrary delimiter can be, a default Latin character set for example, you can see a large number of characters can be used as separators, The following all and 0x10 logic and after non-zero characters can have a delimiter function, MySQL for this does not have any requirements, that is, it is not picky eaters mysql-5.1.61\strings\ctype-latin1.c:static Uchar Ctype_ Latin1[] = {    0,   32, +, 3, +, +, +, +, +, +, +,- 2, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 32, , (+), (+), (+),. 16,  132,132,132,132,132,132,132,132,132,132, +,--------------16,   16,129,129,129,129,129,12 9,  1,  1,  1,  1,  1,  1,  1,  1,  1,    1,  1,  1, &nbs P;1,  1,  1,  1,  1,  1,  1,  1, all, 16,&nbsP  16,130,130,130,130,130,130,  2,  2,  2,  2,  2,  2,  2,  2,  2,     2,  2,  2,  2,  2,  2,  2,  2,  2,  2,  2, 16, 16, 16, 16, 32,   16,  0,  1,  2, +,---------------,  1   0, (+), 16, 16, 16,  2,  2,  0,  2,  1,   72, +----- All, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, and 16, 16,   16, +--------- 6,    1,  1,  1,  1,  1,  1,  1,  1,  1,  1,  1,  1, &nbs P;1,  1,  1,  1,    1,  1,  1,  1,  1,  1,  1,  1 ,  1,  1,  1,  1,  1,  2,    2,  2,  2,  2,  2,  2, &nbsp ; 2,  2,  2,  2,  2,  2,  2,  2,  2,  2,    2,  2,  2,  2,  2,  2, &nbsp ; 2,  2,  2,  2,  2,  2,  2,  2,  2};mysql> Select Date ("2016|01!10 00%00%0 0 "),  time (" 2016-01-10 11%22$33 "), +------------------------------+------------------------------+| Date ("2016|01!10 00%00%00") | Time ("2016-01-10 11%22$33") |+------------------------------+------------------------------+| 2016-01-10                   | 11:22:33                     |+------------------------------+--------- ---------------------+1 Row in Set (0.00 sec)  mysql> four, storage of datetime This is very interesting, jumping out of our usual idea of UTC preservation, so why can it be used in such a concise and intuitive way? The reason is that there is space is wayward, now the UNIX time is usually stored according to long, but the early long type is 32 bytes, so even starting from 1970, the 2038 will produce negative numbers, so it must be compressed, that is, the number of seconds to convert from 1970, It is now stored using a longer storage space, so this is the way to store it directly using this format */Convert time value to integer in YYYYMMDDHHMMSS format */ ulonglong time_to _ulonglong_datetime (const mysql_time *my_time) {  return ((ULONGLONG) (My_time->year * 10000UL +    & nbsp                  my_time->month * 100UL +        &NBSP ;              my_time->day) * ULL (1000000) +          (ULONG) Long) (My_time->hour * 10000UL +                       my_t Ime->minute * 100UL +                      &NBSP;MY_TIME-&GT;SEC ond));} Mysql> Select cast (CAST (' 2007-12-25 ' as DATETIME)  as UNSIGNED INT); +--------------------------------------------------------+| Cast (CAST (' 2007-12-25 ' as DATETIME)  as UNSIGNED INT) |+------------------------------------------------------- -+|                          ,         &NB Sp     20071225000000 |+--------------------------------------------------------+1 row in Set (0.01 sec)   Mysql> v. What type of selection is used in the comparisonLike many non-mandatory types, MySQL allows for automatic conversion between many different types, which involves choosing which data types in the structure are to be aligned, and, of course, if the two types are the same, then everyone is happy. The problem is that you often encounter two variables that are inconsistent in type, and you need to determine what the result is, and you need to do a specific analysis in a specific environment, but there is generally a general principle that embodies mysql-5.1.61\sql\item.cc in a function: Item_result Item_cmp_type (item_result A,item_result b) {if (a = = String_result && b = = String_result) return S  Tring_result;  if (a = = Int_result && b = = Int_result) return int_result;  else if (a = = Row_result | | b = = row_result) return row_result; if (a = = Int_result | | a = decimal_result) && (b = = Int_result | | b = = decimal_result)) return Decimal_r  Esult; return real_result;} As you can see here, if two is a string, the result is a string, but if either of these is an int type, try to move closer to the int type. Vi. SummaryIn general, there is no point in the actual meaning of the problems mentioned here, and there is nothing to say. Just say that for this problem encountered more, or the problem seen here is more common, so I hope you can see from here some other more needs of the process, it is very meaningful.

MySQL for datetime source analysis