关于将表中自增长字段赋值给另外一个字段的方法

 

 

示列代码背景：

数据库名：998pu_test

表名：t_ad_info

需要赋值为自动的列：info_code

 

关于将表中自增长字段赋值给另外一个字段的方法主要可采用以下三种方式：

1      利用mysql系统表

关键SQ如下：

SELECT  Auto_increment            FROM    information_schema.`TABLES`         WHERE    Table_Schema= '998pu_test'        AND table_name = 't_ad_info'

此处关键在于SQL用户具有information_schema库查询权限。

示例：

INSERT INTO t_ad_info (

         info_title,

         supply_demand_type,

         store_subclass_id,

         acreage_scope,

         acreage,

         rent,

         rent_unit,

         house_address,

         map_label,

         linkman,

         telphone,

         administrator_password,

         decoration,

         property_right,

         suit_business_scope,

         info_code,

         ad_info_state,

         sys_user_id,

         area_id,

         city_id,

         district_id,

         store_describe

)

VALUES

         (

                   '12',

                   '1',

                   '92',

                   '1',

                   '12',

                   12,

                   '1',

                   '',

                   '',

                   '111111',

                   '18682025165',

                   '',

                   '1',

                   '1',

                   '121111',

                   (

                            SELECT

                                     Auto_increment

                            FROM

                                     information_schema.`TABLES`

                            WHERE

                                     Table_Schema= '998pu_test'

                            ANDtable_name = 't_ad_info'

                   ),

                   0,

                   '16A1E251!ED31052544E84C668CBBE057437284F3',

                   '259',

                   257,

                   278,

                   '22222222222'

         );

2      @@IDENTITY

使用@@IDENTITY变量，在同一个会话中需要分2步

1）  执行insert语句

2）  查询@@IDENTITY

实例代码：

INSERT INTO t_ad_info (

         info_title,

         supply_demand_type,

         store_subclass_id,

         acreage_scope,

         acreage,

         rent,

         rent_unit,

         house_address,

         map_label,

         linkman,

         telphone,

         administrator_password,

         decoration,

         property_right,

         suit_business_scope,

         info_code,

         ad_info_state,

         sys_user_id,

         area_id,

         city_id,

         district_id,

         store_describe

)

VALUES

         (

                   '12',

                   '1',

                   '92',

                   '1',

                   '12',

                   12,

                   '1',

                   '',

                   '',

                   '111111',

                   '18682025165',

                   '',

                   '1',

                   '1',

                   '121111',

                   (

                            SELECT

                                     Auto_increment

                            FROM

                                     information_schema.`TABLES`

                            WHERE

                                     Table_Schema= '998pu_test'

                            ANDtable_name = 't_ad_info'

                   ),

                   0,

                   '16A1E251!ED31052544E84C668CBBE057437284F3',

                   '259',

                   257,

                   278,

                   '22222222222'

         );

 

SELECT

         @@IDENTITY;

3      LAST_INSERT_ID()

LAST_INSERT_ID()方法是在同一个会话中连续插入使用才有意义，在创建会话的第一条插入语句，插入到info_code的值为1，LAST_INSERT_ID()返回的值为0。

示列代码：

INSERT INTO t_ad_info (

         info_title,

         supply_demand_type,

         store_subclass_id,

         acreage_scope,

         acreage,

         rent,

         rent_unit,

         house_address,

         map_label,

         linkman,

         telphone,

         administrator_password,

         decoration,

         property_right,

         suit_business_scope,

         info_code,

         ad_info_state,

         sys_user_id,

         area_id,

         city_id,

         district_id,

         store_describe

)

VALUES

         (

                   '12',

                   '1',

                   '92',

                   '1',

                   '12',

                   12,

                   '1',

                   '',

                   '',

                   '111111',

                   '18682025165',

                   '',

                   '1',

                   '1',

                   '121111',

                   LAST_INSERT_ID()+1,

                   0,

                   '16A1E251!ED31052544E84C668CBBE057437284F3',

                   '259',

                   257,

                   278,

                   '22222222222'

         );

在本文中，方法2(@@IDENTITY)、3(LAST_INSERT_ID())在同一个会话中具有实际意义。对一条记录的某一列要赋值为自增长相同的值，则需要分2步操作。