使用表联接查询information\u schema.columns表时出错

evrscar2  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(372)

我试图带回不同的b.index\u name和b.column\u name..这就是为什么我使用group by
然后对于返回的每一行,我需要a.data\u类型
a、 返回的数据类型不正确

SELECT  A.DATA_TYPE, B.INDEX_NAME,  B.COLUMN_NAME
FROM  INFORMATION_SCHEMA.COLUMNS as A, INFORMATION_SCHEMA.STATISTICS as B
where B.TABLE_SCHEMA= "mySchema"
and   B.TABLE_NAME  = "myTable"
and   A.TABLE_SCHEMA = B.TABLE_SCHEMA
and   A.TABLE_NAME   = B.TABLE_NAME
GROUP BY B.COLUMN_NAME
42fyovps

42fyovps1#

你的问题是由 GROUP BY 条款挑选 DATA_TYPE 在返回的多个值中随机选择,因为您的 JOIN . 您需要做的是将每个列名的值数限制为1,方法是向 JOIN :

SELECT A.DATA_TYPE, B.INDEX_NAME, B.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS A
JOIN INFORMATION_SCHEMA.STATISTICS AS B
ON A.TABLE_SCHEMA = B.TABLE_SCHEMA AND
   A.TABLE_NAME = B.TABLE_NAME AND
   A.COLUMN_NAME = B.COLUMN_NAME
WHERE A.TABLE_SCHEMA= "mySchema" AND 
      A.TABLE_NAME  = "myTable"

在php中(使用mysqli):

$sql = 'SELECT A.DATA_TYPE, B.INDEX_NAME, B.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS A
JOIN INFORMATION_SCHEMA.STATISTICS AS B
ON A.TABLE_SCHEMA = B.TABLE_SCHEMA AND
   A.TABLE_NAME = B.TABLE_NAME AND
   A.COLUMN_NAME = B.COLUMN_NAME
WHERE A.TABLE_SCHEMA= "mySchema" AND 
      A.TABLE_NAME  = "myTable"';
$result = mysqli_query($conn, $sql);
echo mysqli_num_rows($result);
while ($row = mysqli_fetch_assoc($result)) print_r($row);

相关问题