ps_conv.inc

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

values
  ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                         @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0,
    32.0, 32.0, 32.0 )" ;
execute stmt1 ;
set @arg00= 33.0;
prepare stmt2 from "insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values 
  (  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,   ?,   ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. numeric columns values(CHAR(n),LONGTEXT) #########
insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( '40', '40', '40', '40', '40', '40', '40', '40',
    '40', '40', '40' ) ;
set @arg00= '41' ;
insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                             @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( '42', '42', '42', '42', '42', '42', '42', '42',
    '42', '42', '42' )" ;
execute stmt1 ;
set @arg00= '43';
prepare stmt2 from "insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values 
  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. numeric columns values(BINARY(n),LONGBLOB) ########
insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( CAST('50' as binary), CAST('50' as binary), 
  CAST('50' as binary), CAST('50' as binary), CAST('50' as binary), 
  CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
  CAST('50' as binary), CAST('50' as binary), CAST('50' as binary) ) ;
set @arg00= CAST('51' as binary) ;
insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                             @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( CAST('52' as binary), CAST('52' as binary),
  CAST('52' as binary), CAST('52' as binary), CAST('52' as binary), 
  CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
  CAST('52' as binary), CAST('52' as binary), CAST('52' as binary) )" ;
execute stmt1 ;
set @arg00= CAST('53' as binary) ;
prepare stmt2 from "insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values 
  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. numeric columns values(BIGINT,NULL) ########
# we first assign number to arg00 to set it's datatype to numeric.
set @arg00= 2 ;
set @arg00= NULL ;
insert into t9
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
    NULL, NULL, NULL ) ;
insert into t9
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 61, @arg00, @arg00, @arg00, @arg00, @arg00,
                             @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
    NULL, NULL, NULL )" ;
execute stmt1 ;
prepare stmt2 from "insert into t9
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. numeric columns values(DOUBLE,NULL) ########
set @arg00= 8.0 ;
set @arg00= NULL ;
insert into t9
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 71, @arg00, @arg00, @arg00, @arg00, @arg00,
                             @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
prepare stmt2 from "insert into t9
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. numeric columns values(LONGBLOB,NULL) ########
set @arg00= 'abc' ;
set @arg00= NULL ;
insert into t9
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 81, @arg00, @arg00, @arg00, @arg00, @arg00,
                             @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
prepare stmt2 from "insert into t9
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                    @arg00, @arg00, @arg00, @arg00 ;



######## SELECT of all inserted records ########
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
from t9 where c1 >= 20
order by c1 ;


--disable_query_log
select '-- select .. where numeric column = .. --' as test_sequence ;
--enable_query_log
######## SELECT .. WHERE column(numeric)=value(BIGINT(n)/BIGINT) ########
set @arg00= 20;
select 'true' as found from t9 
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
  and c8= 20 and c9= 20 and c10= 20 and c12= 20;
select 'true' as found from t9 
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00 
  and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
  and c12= @arg00;
prepare stmt1 from "select 'true' as found from t9 
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
  and c8= 20 and c9= 20 and c10= 20 and c12= 20 ";
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9 
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ? 
  and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
  and c12= ? ";
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## SELECT .. WHERE column(numeric)=value(DOUBLE(m,n)/DOUBLE) ########
set @arg00= 20.0;
select 'true' as found from t9 
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
  and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0;
select 'true' as found from t9 
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00 
  and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
  and c12= @arg00;
prepare stmt1 from "select 'true' as found from t9 
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
  and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0 ";
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9 
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ? 
  and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
  and c12= ? ";
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## SELECT .. WHERE column(numeric)=value(CHAR(n)/LONGTEXT) ########
select 'true' as found from t9 
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
  and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20';
prepare stmt1 from "select 'true' as found from t9
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
  and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20' ";
execute stmt1 ;
set @arg00= '20';
select 'true' as found from t9 
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00 
  and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
  and c12= @arg00;
prepare stmt1 from "select 'true' as found from t9 
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ? 
  and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
  and c12= ? ";
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## SELECT .. WHERE column(numeric)=value(BINARY(n)/LONGBLOB) ########
select 'true' as found from t9 
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and 
      c3= CAST('20' as binary) and c4= CAST('20' as binary) and 
      c5= CAST('20' as binary) and c6= CAST('20' as binary) and 
      c7= CAST('20' as binary) and c8= CAST('20' as binary) and 
      c9= CAST('20' as binary) and c10= CAST('20' as binary) and 
      c12= CAST('20' as binary);
prepare stmt1 from "select 'true' as found from t9
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and 
      c3= CAST('20' as binary) and c4= CAST('20' as binary) and 
      c5= CAST('20' as binary) and c6= CAST('20' as binary) and 
      c7= CAST('20' as binary) and c8= CAST('20' as binary) and 
      c9= CAST('20' as binary) and c10= CAST('20' as binary) and 
      c12= CAST('20' as binary) ";
execute stmt1 ;
set @arg00= CAST('20' as binary) ;
select 'true' as found from t9 
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00 
  and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
  and c12= @arg00;
prepare stmt1 from "select 'true' as found from t9 
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ? 
  and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
  and c12= ? ";
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;

delete from t9 ;

#################### Some overflow experiments ################################
#                                                                             #
# MySQL Manual (July 2004)                                                    #
# - Setting a numeric column to a value that lies outside the column's range. #
#   The value is clipped to the closest endpoint of the range.                #
# ...                                                                         #
# - For example, inserting the string '1999.0e-2' into an INT, FLOAT,         #
#   DECIMAL(10,6), or YEAR column results in the values 1999, 19.9921,        #
#   19.992100, and 1999.                                                      #
# That means there is an anomaly if a float value is assigned via string to   #
# a column of type bigint. The string will be cut from the right side to      #
# a "usable" integer value.                                                   #
#                                                                             #
###############################################################################
--disable_query_log
select '-- some numeric overflow experiments --' as test_sequence ;
--enable_query_log
prepare my_insert from "insert into t9 
   ( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values 
   ( 'O',  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,   ?,   ? )" ;
prepare my_select from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
from t9 where c21 = 'O' ";
prepare my_delete from "delete from t9 where c21 = 'O' ";

# Numeric overflow of columns(c1, c2, c3, c4, c5, c12) with type not in 
# (BIGINT,FLOAT,REAL,DOUBLE) during insert
#
# Use the maximum BIGINT from the manual
set @arg00= 9223372036854775807 ;
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                        @arg00, @arg00, @arg00, @arg00, @arg00 ;
--vertical_results
--replace_result e+0 e+
execute my_select ;
--horizontal_results
--replace_result e+0 e+
execute my_delete ;
set @arg00= '9223372036854775807' ;
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                        @arg00, @arg00, @arg00, @arg00, @arg00 ;
--vertical_results
--replace_result e+0 e+
execute my_select ;
--horizontal_results
--replace_result e+0 e+
execute my_delete ;
# Use the minimum BIGINT from the manual
#
set @arg00= -9223372036854775808 ;
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                        @arg00, @arg00, @arg00, @arg00, @arg00 ;
--vertical_results
--replace_result e+0 e+
execute my_select ;
--horizontal_results
--replace_result e+0 e+
execute my_delete ;
set @arg00= '-9223372036854775808' ;
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                        @arg00, @arg00, @arg00, @arg00, @arg00 ;
--vertical_results
--replace_result e+0 e+
execute my_select ;
--horizontal_results
--replace_result e+0 e+
execute my_delete ;