Following the principle that “it’s not yours unless it’s on your own hard drive,” and with my phone’s growing image collection desperately needing an offload to save storage, I decided to download and organize artworks by artist. I needed a way to record progress and query/update data quickly. I originally used a Markdown list, but as the artist count grew, searching became way too slow.

Two days later, it clicked: for “logging” stuff, a database is obviously the best tool for the job!

Download and Installation

I decided to go with MySQL. For installation, you can refer to guides like MySQL Download and Installation - Zhihu . Some details might have changed, but the process is largely the same.

I just went with the default installation—clicking “Next” until it was done (too lazy to customize).

To start the service, hit Win+R, type services.msc, find the MySQL service, and start it.

Database Design

To be honest, there’s not much to “design” here. I mainly download from Pixiv and some from Twitter, so a single table is enough.

Which normal form is this? Who cares. Here is the code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Create database
create database hello;

-- Select database
use hello;

-- Create table
create table pic
(
    ID varchar(99),
    name varchar(255),
    downloadedDate datetime,
    lastDownloadID varchar(255),
    platform varchar(50),
    url varchar(255),
    primary key(ID)
);

Downloading Artworks

• Pixiv

I use the “Powerful Pixiv Downloader” extension. Chrome Web Store Link

GitHub: xuejianxianzun/PixivBatchDownloader

Note: Enable slow crawling. Downloading too fast or too much at once will result in a temporary IP ban.

• Twitter

I use the “Twitter Media Downloader” extension. Chrome Web Store Link

Official site: Twitter Media Downloader - furyu

Data Insertion

1
2
3
4
5
6

-- Select database
use hello;

-- Insert data
insert into pic values
('6049901', '鬼针草', '2023-02-09', '105176620', 'pixiv', 'https://www.pixiv.net/users/6049901')

For multiple rows, just separate them with a comma ,.

Updating Data

1
2
3

update pic
set downloadedDate = '2023-03-02', lastDownloadID = '105716156'
where ID = '6049901'

Learn more: Database Learning Part 3: SQL Language – yexca’s Blog

MySQL Service Missing?

Here is a quick fix for a problem I encountered. When I tried to open the database to import data, I found that the MySQL service was missing from my Windows Services list.

Run CMD as Administrator and navigate to the MySQL/bin directory:

1
2
3
4

# Register the service
mysqld --install
# Start the service
net start mysql

It should be up and running in a few seconds, and no data was lost.

New to Databases?

My intro: Database Learning Part 1: Intro to Databases – yexca’s Blog

Runoob: SQL Tutorial - Runoob

Reference Articles

Common MySQL Table Options and Constraints - GeaoZhang - Blog Garden