Codeigniter:选择所有指定列的前缀字符串

Question

我有以下选择查询。

    $this->db->select("$order_table.user_id, $order_table.total_price,
                       $order_table.shipping, $order_table.tax, 
                       $order_table.filled, $order_table.shipped, 
                        $order_table.tracking");
    $this->db->select("$user_table.first_name as user_first_name, 
                       $user_table.last_name as user_last_name, 
                       $user_table.email as user_email");
    $this->db->select("$mailing_address_table.first_name as mailing_first_name, 
                       $mailing_address_table.last_name as mailing_last_name, 
                       $mailing_address_table.address as mailing_address, 
                       $mailing_address_table.address_2 as mailing_address_2, 
                       $mailing_address_table.city as mailing_city, 
                       $mailing_address_table.state as mailing_state, 
                       $mailing_address_table.zip_code as mailing_zip_code, 
                       $mailing_address_table.country as mailing_country");

    $this->db->select("$billing_address_table.first_name as billing_first_name, 
                       $billing_address_table.last_name as billing_last_name, 
                       $billing_address_table.address as billing_address, 
                       $billing_address_table.address_2 as billing_address_2, 
                       $billing_address_table.city as billing_city, 
                       $billing_address_table.state as billing_state,              
                       $billing_address_table.zip_code as billing_zip_code, 
                       $billing_address_table.country as billing_country");

    $this->db->join($user_table, 
                   "$user_table.id = $order_table.user_id", "left");
    $this->db->join($mailing_address_table, 
                   "$mailing_address_table.user_id = $user_table.id", "left");
    $this->db->join($billing_address_table, 
                   "$billing_address_table.user_id = $user_table.id", "left");

不管怎么说，我设想使用CONCAT，我可以在我在计费中选择的每一列中前缀"billing_“。当我不选择所有的内容，而只是选择特定的列时，我似乎无法理解这一点。

Id喜欢我从$user_table中选择的每一列都是前缀，包括"user_“、$mailing_table "mailing_”等等。

注：

1)我没有从这些select查询中的每一列中选择所有字段，所以我无法从.

2)我不想更新数据库中的列名，只是为了进行特定的选择使用。

Answer 1

在这里，您可以创建到字段列表：

SET @SCHEMA = 'toimport';

SET @PREFIX = 'billing_';
SET @POSTFIX = 'address';

SELECT GROUP_CONCAT(
 CONCAT(@SCHEMA,'.',@PREFIX,'.',@POSTFIX,'.',COLUMN_NAME,
 ' AS ',@PREFIX,COLUMN_NAME) SEPARATOR ',\n')
 FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = @SCHEMA AND TABLE_NAME = CONCAT( @PREFIX,  @POSTFIX );

结果如下：

toimport.billing_.address.id AS billing_id,
toimport.billing_.address.name AS billing_name,
toimport.billing_.address.street AS billing_street,
toimport.billing_.address.a1 AS billing_a1,
toimport.billing_.address.a2 AS billing_a2

Answer 2

以下是您对更多表格的回答：

查询：

SET @SCHEMA = 'toimport';

SELECT
  GROUP_CONCAT(
    CONCAT(
      @SCHEMA,'.',
      SUBSTRING_INDEX(TABLE_NAME, '_', 1),'.',
      SUBSTRING_INDEX(TABLE_NAME, '_', -1),'.',
      COLUMN_NAME, ' AS ',
      SUBSTRING_INDEX(TABLE_NAME, '_', 1),'_',COLUMN_NAME
    ) SEPARATOR ',\n'
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @SCHEMA
  AND TABLE_NAME IN ('billing_address', 'email_post');

其结果是：

toimport.billing.address.id AS billing_id,
toimport.billing.address.name AS billing_name,
toimport.billing.address.street AS billing_street,
toimport.billing.address.a1 AS billing_a1,
toimport.billing.address.a2 AS billing_a2,
toimport.email.post.id AS email_id,
toimport.email.post.name AS email_name,
toimport.email.post.street AS email_street,
toimport.email.post.a1 AS email_a1,
toimport.email.post.a2 AS email_a2