mysql 创建虚拟字段创建index

  SELECT n_table.property_id,
         n_table.address,
         now() AS last_operation_date,
         n_table.lat,
         n_table.lon,
         n_table.beds,
         n_table.sqft,
         n_table.baths,
         n_table.price,
         n_table.lot_size
    FROM (SELECT DISTINCT
                 cast(JSON_EXTRACT(rj.json_data, '$.property_id') AS SIGNED)
                    AS property_id,
                 JSON_EXTRACT(rj.json_data, '$.address')
                    AS address,
                 JSON_EXTRACT(rj.json_data, '$.lat')
                    AS lat,
                 JSON_EXTRACT(rj.json_data, '$.lon')
                    AS lon,
                 JSON_EXTRACT(rj.json_data, '$.beds')
                    AS beds,
                 JSON_EXTRACT(rj.json_data, '$.sqft')
                    AS sqft,
                 JSON_EXTRACT(rj.json_data, '$.baths')
                    AS baths,
                 JSON_EXTRACT(rj.json_data, '$.price')
                    AS price,
                 JSON_EXTRACT(rj.json_data, '$.lot_size')
                    AS lot_size
            FROM america_estate_original_db.tb_realtor_list_page_json rj) n_table
   WHERE n_table.property_id IS NOT NULL

 

 

 

CREATE INDEX tb_realtor_list_page_json_property_id_index
   ON america_estate_original_db.tb_realtor_list_page_json(property_id);

 

 

ALTER TABLE america_estate_original_db.tb_realtor_list_page_json
   ADD COLUMN property_id VARCHAR(200)
                CHARACTER SET utf8
                COLLATE utf8_general_ci
                GENERATED ALWAYS
                   AS(cast(
                         JSON_EXTRACT(rj.json_data, '$.property_id')
                            AS SIGNED))
                   VIRTUAL
                NULL