博客 / 詳情

返回

效能工具（九）之編寫nodejs腳本使用get-video-duration批量讀取視頻時長，並生成sql語句修復數據庫表字段值

前言

看文，看的是一種思路，希望筆者的文章能給諸位帶來一些靈感思路☺️☺️☺️

場景概述

實際場景描述有些冗餘，特抽象出來描述如下：

公司平台上有一個視頻文件列表，列表有近千條數據，對應接口返回的就是一個數組，length將近1000，如：

let tableData = [
    { id: 1, name: '視頻1', duration: 364, url: 'https://abc.com.cn/files/1.mp4' },
    { id: 2, name: '視頻2', duration: 210, url: 'https://abc.com.cn/files/2.mp4' },
    ...
    { id: 999, name: '視頻999', duration: 540, url: 'https://abc.com.cn/files/999.mp4' },
]
其中，name是視頻名字，duration是視頻時長（秒），url是視頻靜態資源可訪問地址
  • 現在的問題是，因為一些無語的原因，這999條數據中，存在部分數據存儲的duration字段的值不對，和實際視頻的時長對不上，或多或少
  • 比如第一條數據，視頻1的實際時長是464秒，但duration字段值存的是364秒
  • 現在需要將其修復成正確的時長，但是因為數據量太多，近千條數據，人工一條一條核對，效率十分低下，而且人工核對容易出錯

因此筆者寫了一個nodejs腳本，批量執行效率高，質量有保障

解決方案思路

  • 首先，循環tableData得到數組的每一項
  • 然後，使用get-video-duration這個包，讀取每一項的url對應的視頻資源
  • 得到對應視頻真正的時長，比如叫做trueDurationNum，和當前的duration對比一下
  • 若相同，則代表時長沒問題；若不同，則單獨拎出來丟到notEqualArr數組裏
  • 最後，再把notEqualArr統一循環處理
  • 或調用修改接口，批量請求修改成正確的視頻時長
  • 或者寫個函數將其轉成sql語句，直接一條命令執行解決問題

get-video-duration包介紹

get-video-duration 是github上的擁有140個Star的小眾包，傳給它一個視頻的url，它就可以返回此視頻對應的時長信息，如下

const { getVideoDurationInSeconds } = require('get-video-duration')
const duration = await getVideoDurationInSeconds(url)
console.log('視頻時長/秒', duration)

支持mp4、mov、多種視頻格式，其底層依賴FFmpeg的ffprobe套件，能夠分析音視頻（比如分辨率、編碼格式、時長）其依賴簡約如下：

{
  "name": "get-video-duration",
  "description": "Get the duration of a video file",
  "version": "4.1.0",
  "author": "Lluís Ulzurrun de Asanza Sàez <me@llu.is> (http://llu.is)",
  "license": "MIT",
  "repository": "caffco/get-video-duration",
  "main": "dist/commonjs/index.js",
  "module": "dist/es6/index.js",
  "dependencies": {
    "@ffprobe-installer/ffprobe": "^2.1.2",
    "execa": "^5.0.0",
    "is-stream": "^2.0.0"
  },
  ......
}
實際上，包的作者除了有這個獲取視頻時長的工具包之外，還有一個獲取音頻時長的包：get-audio-duration

代碼實現

因為要安裝包，所以要npm inti -y簡單創建一個node工程

而後安裝對應依賴npm i get-video-duration

{
  "name": "video-duration-check",
  "version": "1.0.0",
  "main": "app.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": "",
  "dependencies": {
    "get-video-duration": "^4.1.0"
  }
}

檢查是否有不相等的視頻時長項

// app.js
const { getVideoDurationInSeconds } = require('get-video-duration')
const fs = require('fs')
// 給到一個url，返回視頻時長
async function getVideoDuration(url) {
    try {
        const duration = await getVideoDurationInSeconds(url)
        return Math.floor(duration) // 向下取整時長，精度到秒
        // return duration // 精準時長，精度到毫秒
    } catch (error) {
        throw error
    }
}
// 接口返回數據的示例
const tableData = [
    { id: 1, name: '視頻1', duration: 364, url: 'https://abc.com.cn/files/1.mp4' },
    { id: 2, name: '視頻2', duration: 210, url: 'https://abc.com.cn/files/2.mp4' },
    { id: 999, name: '視頻999', duration: 540, url: 'https://abc.com.cn/files/999.mp4' },
]
async function main() {
    // 存儲不匹配的數據
    const notEqualArr = []
    // 遍歷tableData，讀取每個視頻的時長
    for (const item of tableData) {
        try {
            const trueDurationNum = await getVideoDuration(item.url) // 讀取時長
            if (item.duration !== trueDurationNum) { // 如果時長不匹配，則存入notEqualArr
                // 把真實的時長也存到item中，方便後續處理，請求修改接口，或者生成sql語句
                item.trueDurationNum = trueDurationNum
                notEqualArr.push(item)
            }
        } catch (error) {
            // 也可以在這裏處理錯誤，比如新建一個errorArr，把錯誤信息存進去
            console.error(error)
        }
    }
    console.log(`共有${tableData.length}個視頻，其中不匹配${notEqualArr.length}個`)
    fs.writeFileSync('notEqualArr.json', JSON.stringify(notEqualArr, null, 2))
    console.log('notEqualArr.json 文件已保存')
}
main()

經過這樣一波操作，就能得到duration不對的notEqualArr數據項了，假設如下：

const notEqualArr = [
    { id: 3, name: '視頻3', duration: 364, trueDurationNum: 3333, url: 'https://abc.com.cn/files/3.mp4' },
    { id: 4, name: '視頻4', duration: 210, trueDurationNum: 4444, url: 'https://abc.com.cn/files/4.mp4' },
]

現在，有了不相等的數據了，可以選擇兩種方案

  • 一種是循環notEqualArr數組，然後通過編輯接口修改duration的值為trueDurationNum的值
  • 第二種是拼接成對應的sql，直接通過DBeaver或者Navicat 直接一條sql搞定

方式一：通過編輯接口修改

如下示例思路代碼

// 編輯接口
const baseUrl = 'https://abc.com.cn/api/editVideo'
// 登錄系統後，複製一份請求頭的 Authorization
const Authorization = 'Bearer eyJhbGci...'
// 存儲不匹配的數據
const notEqualArr = [
    { id: 3, name: '視頻3', duration: 364, trueDurationNum: 3333, url: 'https://abc.com.cn/files/3.mp4' },
    { id: 4, name: '視頻4', duration: 210, trueDurationNum: 4444, url: 'https://abc.com.cn/files/4.mp4' },
]
// 循環發請求修改對應duration字段的值為trueDurationNum
async function main() {
    console.time('main')
    for (const item of notEqualArr) {
        try {
            const res = await fetch(baseUrl, {
                headers: {
                    'Authorization': Authorization,
                    'Content-Type': 'application/json'
                },
                method: 'POST',
                body: JSON.stringify({
                    id: item.id,
                    duration: item.trueDurationNum
                })
            })
            const { data } = await res.json()
            console.log(`更新成功 - ID: ${item.id}, 新的時長: ${item.trueDurationNum}`)
            console.log(data)
        } catch (error) {
            console.error(`更新失敗 - ID: ${item.id}, 錯誤: ${error.message}`)
        }
    }
    console.timeEnd('main')
}
main()

方式二：通過sql修改

回顧一下

  • 假設，我要批量修改student表裏面的
  • id為2的那條數據，將其年齡改為22
  • id為3的那條數據，將其年齡改為33，寫法如下
UPDATE student
SET age = CASE id
    WHEN 2 THEN 22
    WHEN 3 THEN 33
END
WHERE id IN (2, 3);

合併成為一行語法

UPDATE student SET age = CASE id WHEN 2 THEN 22 WHEN 3 THEN 33 END WHERE id IN (2, 3);

對應上述修改duration的寫法就是（假設表是video_table）

const notEqualArr = [
    { id: 3, name: '視頻3', duration: 364, trueDurationNum: 3333, url: 'https://abc.com.cn/files/3.mp4' },
    { id: 4, name: '視頻4', duration: 210, trueDurationNum: 4444, url: 'https://abc.com.cn/files/4.mp4' },
]
// 換行
UPDATE video_table
SET duration = CASE id
    WHEN 3 THEN 3333
    WHEN 4 THEN 4444
END
WHERE id IN (3, 4);

// 不換行，一行語句就是
UPDATE video_table SET duration = CASE id WHEN 3 THEN 3333 WHEN 4 THEN 4444 END WHERE id IN (3, 4);

所以，只需要寫一個函數，將數組notEqualArr轉成對應的單條sql語句即可

轉換函數寫法如下

const notEqualArr = [
    { id: 3, name: '視頻3', duration: 364, trueDurationNum: 3333, url: 'https://abc.com.cn/files/3.mp4' },
    { id: 4, name: '視頻4', duration: 210, trueDurationNum: 4444, url: 'https://abc.com.cn/files/4.mp4' },
]
/**
 * 將數組轉換為批量更新 SQL 語句
 * @param {Array} arr - 包含 id 和 trueDurationNum 的數組
 * @param {string} tableName - 表名，默認為 'video_table'
 * @returns {string} 生成的 SQL 語句
 */
function generateSql(arr, tableName = 'video_table') {
    // 構建 CASE WHEN 語句
    const caseStatements = arr.map(item =>
        `WHEN ${item.id} THEN ${item.trueDurationNum}`
    ).join(' ');
    console.log('caseStatements---->', caseStatements); // WHEN 3 THEN 3333 WHEN 4 THEN 4444
    // 構建 IN 條件
    const ids = arr.map(item => item.id).join(',');
    console.log('ids---->', ids); // 3,4
    // 生成完整的 SQL 語句
    const sql = `UPDATE ${tableName} SET duration = CASE id ${caseStatements} END WHERE id IN (${ids});`;
    return sql;
}
function main() {
    const sql = generateSql(notEqualArr);
    return sql;
}
console.log(main());
// UPDATE video_table SET duration = CASE id WHEN 3 THEN 3333 WHEN 4 THEN 4444 END WHERE id IN (3,4);

最後，筆者採取方式二，直接sql執行的方案（先在測試環境試一下）最終快速解決了這個視頻時長不對的問題

注意，這裏的notEqualArr筆者是直接寫到代碼裏面，不是將其丟到一個.json文件裏面，再const notEqualArr = require('./notEqualArr.json')引入進來，這樣就能避免require緩存機制，可參見這篇文章：請不要使用require引入單個文件

A good memory is better than a bad pen. Record it down...☺️☺️☺️
node.js , SQL
user avatar nanian_5cd6881d3cc98 頭像 _5f356707606d9 頭像 tudou_5e2d76940fbaa 頭像 bestvist 頭像 u_16099278 頭像 wentaohu12138 頭像
6 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.