Laravel 5.4导入Excel月度数据至MySQL的最优方案咨询

Hey there, let's break down your problem and figure out the best approach for your Laravel app. Your core pain points are handling monthly time-series data efficiently, supporting search/filtering, and feeding data to Highcharts—so let's go through the options you mentioned, plus the most practical solution for your tech stack:

You already started with Item and ItemMonthly models, and this is actually the most robust fit for your use case. The "繁琐" (cumbersome) feeling you're having is likely from suboptimal implementation, not the pattern itself. Here's how to make it work smoothly:

Table Structure #

• items table: Store your fixed columns: item_group, item_number, item_name, item_currency, plus standard timestamps.
• item_monthly_data table:
  • item_id (foreign key to items.id)
  • month (DATE type, e.g., 2017-12-01 representing the start of the month)
  • value (DECIMAL/FLOAT, depending on your data precision needs)
  • Add a unique composite index on item_id + month to prevent duplicate imports for the same item/month.

Why This Works #

• Flexibility for incomplete data: Missing months simply don't have a row in item_monthly_data—no need to handle nulls or empty columns.
• Easy charting: When fetching data for Highcharts, just query with orderBy('month') to get perfectly ordered time-series data. Example Eloquent query:

  $item->monthlyData()->orderBy('month')->get(['month', 'value']);
  

  This gives you a ready-to-use dataset for Highcharts without extra parsing.
• Powerful filtering/search: Use Laravel's Eloquent to combine item attributes with monthly data filters. For example, get all items in a group with 2023 data:

  Item::where('item_group', 'Electronics')
      ->with(['monthlyData' => function($query) {
          $query->whereYear('month', 2023);
      }])
      ->get();
  

• Maintainable: No need to alter table structures when new months are added—just insert new rows.

Import Optimization Tips #

When parsing your Excel with maatwebsite/excel:

1. First, create/update the Item record using the fixed columns (Item group, item #, etc.).
2. Loop through all monthly columns:
   • Parse the column name (e.g., "dec 2017") into a Carbon date with Carbon::parse($columnName)->startOfMonth().
   • If the cell has a valid value, use firstOrCreate to avoid duplicates:

     $item->monthlyData()->firstOrCreate(
         ['month' => $parsedMonth],
         ['value' => $cellValue]
     );
     

Let's quickly cover the alternatives you're considering to clarify why they're less ideal for your needs:

MySQL JSON Column #

Storing monthly data as a JSON object (e.g., {"2017-12": 100, "2018-01": 150}) in a single column on the items table:

• Pros: Simple table structure, quick to implement for imports.
• Cons:
  • Poor performance for filtering: Querying all items with a specific month's value requires JSON path functions, which are slower and less intuitive than relational queries.
  • Charting hassle: You'll need to extract, sort, and format JSON data manually to get ordered time-series for Highcharts—far more work than just sorting a month column.
  • Hard to update: Changing a single month's value means rewriting the entire JSON object, which is inefficient.

MongoDB #

Switching to a document database:

• Pros: Flexible schema, easy to store nested monthly data directly in item documents.
• Cons:
  • Tech stack friction: You'll need to integrate Laravel with MongoDB (via packages like jenssegers/laravel-mongodb), adding maintenance complexity if your app is already MySQL-focused.
  • Weak relational queries: Filtering by item group + monthly ranges is less straightforward than with MySQL's joins and where clauses.
  • Overkill for your use case: Your data has a clear relational structure (items have many monthly entries)—MongoDB shines with unstructured data, not well-defined time-series relationships.

Stick with the relational Item + ItemMonthly table approach. It's the most aligned with your requirements (search, filtering, charting) and fits seamlessly with your existing Laravel 5.4 + MySQL stack. With a few tweaks to your import logic and indexing, it'll eliminate the "繁琐" feeling and scale well as your data grows.

内容的提问来源于stack exchange，提问作者Shuyinsama