从SHOW COLUMNS mysql中排除某些列

I have this query: SHOW COLUMNS FROM mash which works fine in my while loop for building a select element made from table column names. But in my table i have "id" and "tstamp" which i dont want in the select element, is this possible to exclude these columns?

我有这个查询:SHOW COLUMNS FROM mash在我的while循环中正常工作,用于构建由表列名制成的select元素。但是在我的表中,我有“id”和“tstamp”,我不想在select元素中,这是否可以排除这些列?

echo "<form action='".$_SERVER['PHP_SELF']."' method='get'>";
            connectDB();
            $result = mysql_query("SHOW COLUMNS FROM mash") or die(mysql_error());
            echo '<select name="column" class="column">';
            while ($row = mysql_fetch_array($result)) {
                echo "<option value='".$row[0]."'>".ucwords($row[0])."</option>";
            }
            closeConn();
            echo '</select>';
echo "</form>";

4 个解决方案

#1

PHP Way:

Use a continue in the while loop, when those fields are fetched, like this:

在获取这些字段时,在while循环中使用continue,如下所示:

echo "<form action='".$_SERVER['PHP_SELF']."' method='get'>";
    connectDB();
    $result = mysql_query("SHOW COLUMNS FROM mash") or die(mysql_error());
    echo '<select name="column" class="column">';
    while ($row = mysql_fetch_array($result))
    {
        if($row[0] == 'id' || $row[0] == 'tstamp')
              continue;
        echo "<option value='".$row[0]."'>".ucwords($row[0])."</option>";
    }
    closeConn();
    echo '</select>';
echo "</form>";

This will just skip the id and tstamp fields and process all others. continue is used within looping structures to skip the rest of the current loop iteration and continue execution at the condition evaluation and then the beginning of the next iteration.

这将跳过id和tstamp字段并处理所有其他字段。 continue在循环结构中用于跳过当前循环迭代的其余部分,并在条件评估和下一次迭代开始时继续执行。

---

MySQL Way:

Remove those fields in the query like this:

删除查询中的这些字段,如下所示:

SHOW COLUMNS FROM mash WHERE Field NOT IN ('id', 'tstamp');

#2

Yes, it's possible. Instead of using SHOW COLUMNS use INFORMATION_SCHEMA.

是的,这是可能的。而不是使用SHOW COLUMNS使用INFORMATION_SCHEMA。

INFORMATION_SCHEMA is the ANSI way of extracting metadata from a relational database. In MySQL you can use:

INFORMATION_SCHEMA是从关系数据库中提取元数据的ANSI方法。在MySQL中,您可以使用:

$sql = "select column_name from information_schema.columns c " + 
       " where c.table_schema = 'db_name' " + 
       "   and c.table_name='table_name' " + 
       "   and c.column_name like 'name%'";

Information Schema has the advantage that is SQLANSI compliant. You can use it in MySQL, PostgreSQL, SQLServer and other relational databases.

信息模式具有符合SQLANSI的优点。您可以在MySQL,PostgreSQL,SQLServer和其他关系数据库中使用它。

#3

You can use LIKE operator.

您可以使用LIKE运算符。

SHOW COLUMNS FROM mash LIKE "name%"

#4

To see just the "Collation" column that shows up in SHOW FULL COLUMNS from tablename for just one field, it's:

要查看仅在一个字段中从表名显示在SHOW FULL COLUMNS中的“排序规则”列,它是:

select COLLATION_NAME from information_schema.columns
where TABLE_SCHEMA = 'tableschemaname' and TABLE_NAME = 'tablename' and COLUMN_NAME = 'fieldname';

Ddrop the fieldname in the where, if you want to see that column for all field names.

如果要查看所有字段名称的列,请在where中删除字段名称。

To see all possible column names that show up from SHOW FULL COLUMNS so that you can select what you want:

要查看从SHOW FULL COLUMNS显示的所有可能的列名称,以便您可以选择所需的:

select * from information_schema.columns 
where TABLE_SCHEMA = 'tableschemaname' and TABLE_NAME = 'tablename' and COLUMN_NAME = 'fieldname';