从MySQL表生成报表 [英] Generating reports from MySQL tables

问题描述

假设您有一堆MySQL表，您希望最终用户能够使用PHP脚本生成具有该数据的报告。您可以在下拉列表中显示这些表格中的字段名称，因此用户可能会说： first_name 等于John。大。但是如果你想让这些字段名字更容易读取呢？例如，我希望用户能够选择第一宠物的名称作为字段，而不是 first_pet_name 。我绝对不想将该信息存储在标记中，因为我们可能会频繁地添加和删除表。

解决方案

我将其存储在数据库中。

  CREATE TABLE human_labels（
 schema varchar（64）not null，
 table varchar（64）not null，
 column varchar（64）not null，
 label tinytext not null，
主键（schema，table，column）
）; 
  

哪里的模式是你通常在mysql中调用数据库（后来的 USE 当您切换数据库时）;而且表和列是相当明显的。

当然，您必须确保在更改模式时DBA更新。

我相信MySQL允许对表进行评论，但不能列出一个列，或者您可以使用该列。

修改：将varchar更改为64，因为这是MySQL手动文档的最大大小。此外，事实证明，如果您愿意，您可以对每一列进行评论，您可以从information_schema.columns中阅读。但是我仍然以上面的方式来做，因为它更灵活（你可以轻松地把额外的数据放在那里，比如你的我应该显示这个字段的标志），并且还允许评论用于他们的预期目的。 / p>

Let's say you have a bunch of MySQL tables, and you want your end users to be able to generate reports with that data with a PHP script. You can present the field names from those tables in a dropdown, so a user might be able to say, "first_name equals John." Great. But what if you want those field names to be a little more readable? For instance, I'd like the user to be able select "Name of First Pet" as a field, instead of "first_pet_name." I definitely don't want to store that information in the markup, as we might be adding and removing tables pretty frequently. What's the simplest way to pull this off?

解决方案

I'd store it in the database.

CREATE TABLE human_labels (
    schema  varchar(64) not null,
    table   varchar(64) not null,
    column  varchar(64) not null,
    label   tinytext    not null,
    primary key (schema, table, column)
);

Where schema is what you commonly call "database" in mysql (what goes after USE when you switch databases); and table and column are pretty obvious.

Of course, you'll have to make sure the DBA updates that whenever changing the schema.

I believe MySQL allows a comment on a table, but not a column, or you could use that.

Edit: Changed the varchar to 64 because that's what the MySQL manual documents as the max size. Also, it turns out you can put a comment on each column if you wish — and you can read those back from information_schema.columns. But I'd still do it the way shown above as its more flexible (you can put additional data in there easily, such as your "should I show this field" flag) and also allows comments to be used for their intended purpose.

这篇关于从MySQL表生成报表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！