从洋红色表结构获取客户信息

可以从Magento MySQL DB中提取一些数据，但在执行以下查询之前，请注意以下事项：

1）Magento是可配置的，因此您不得从MySQL获取数据，但必须使用Magento客户模块（模型，资源模型）和Magento配置数据来检索客户数据。任何直接查询都不能保证与不同的Magento版本兼容，甚至与同一版本的安装兼容。

2）Magento EAV结构不允许您在一个查询中以良好的形式获取所有客户数据，因为表名与属性动态匹配。

3）您不能仅通过一个查询甚至多个查询提取所有客户数据。因为许多信息对象都是在模型中创建的，只有模型具有在一个客户对象中收集所有数据的逻辑。我谈论送货/账单地址，商店信用，奖励积分（EE版本）等。

但是，为了获取所有客户的 varchar 属性，您可以使用以下代码（由于上述 1 中所述，不能保证工作）：

SELECT ce.*, ea.attribute_code, cev.value
  FROM customer_entity AS ce 
  LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id AND ea.backend_type = 'varchar'
  LEFT JOIN customer_entity_varchar AS cev ON ce.entity_id = cev.entity_id AND ea.attribute_id = cev.attribute_id

此外，还可以使用此类查询来提取客户的所有属性

SELECT ce.*, ea.attribute_code, 
    CASE ea.backend_type 
       WHEN 'varchar' THEN ce_varchar.value
       WHEN 'int' THEN ce_int.value
       WHEN 'text' THEN ce_text.value
       WHEN 'decimal' THEN ce_decimal.value
       WHEN 'datetime' THEN ce_datetime.value
       ELSE NULL
    END AS value
  FROM customer_entity AS ce 
  LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
  LEFT JOIN customer_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
  LEFT JOIN customer_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
  LEFT JOIN customer_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
  LEFT JOIN customer_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
  LEFT JOIN customer_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'

为了补充@Stefano的回应，我做了一个很大的查询，只是为了提取一些其他重要细节，比如地址和电话。其中一些细节只在我的数据库上有意义，但可以帮助某人：

SELECT c.entity_id,c.email, 
            (
            SELECT fn.value
            FROM customer_entity_varchar fn
            WHERE c.entity_id = fn.entity_id AND 
             fn.attribute_id = 12) AS password_hash, 
             (
            SELECT fn.value
            FROM customer_entity_varchar fn
            WHERE c.entity_id = fn.entity_id AND
             fn.attribute_id = 5) AS name, 
             (
            SELECT fn.value
            FROM customer_entity_varchar fn
            WHERE c.entity_id = fn.entity_id AND 
             fn.attribute_id = 7) AS lastname,
             (
            SELECT fn.value
            FROM customer_entity_varchar fn
            WHERE c.entity_id = fn.entity_id AND 
             fn.attribute_id = 150) AS cpfcnpj, 
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 149) AS cpfcnpj2,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 145) AS rg,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 151) AS phone1,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 31) AS phone2,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 27) AS country,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 28) AS state,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 26) AS city,                 
             cat.value AS address,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 30) AS cep
            FROM customer_entity AS c
            LEFT JOIN customer_address_entity AS ca ON c.entity_id = ca.parent_id
            LEFT JOIN customer_address_entity_text AS cat ON cat.entity_id = ca.entity_id                

            GROUP BY entity_id