示列代碼背景：

數(shù)據(jù)庫名：998pu_test

表名：t_ad_info

需要賦值為自動的列：info_code

關于將表中自增長字段賦值給另外一個字段的方法主要可采用以下三種方式：

1 利用mysql系統(tǒng)表

關鍵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）  執(zhí)行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()方法是在同一個會話中連續(xù)插入使用才有意義，在創(chuàng)建會話的第一條插入語句，插入到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步操作。