/// BANGBOO BLOG ///

=============

# 追加オプションを使った接続も藹??

connector = Connector(

ip_type="public", # "private" また縺? "psc" も使用可閭?

enable_iam_auth=False,

timeout=30,

credentials=None, # 必要ならGoogle認証情報を渡す

refresh_strategy="lazy", # "lazy" また縺? "background"

)

#トランザクショ繝?

try:

conn = sql_getconn(connector)

conn.autocommit = False # トランザクション開始、あるい縺? conn.begin()

cursor = conn.cursor()

# 挿入するデータを準備

new_data = [

{"no": 4, "name": "新しい名前4", "targetDate": "2024-05-01"},

{"no": 5, "name": "新しい名前5", "targetDate": "2024-05-02"},

]

# INSERT文を構築して藹??行

for data in new_data:

sql = "INSERT INTO test (no, name, targetDate) VALUES (%s, %s, %s)"

values = (data["no"], data["name"], data["targetDate"])

cursor.execute(sql, values)

conn.commit() # トランザクションをコミット

print("Data inserted successfully.")

except Exception as e:

conn.rollback() # エラーが発生した場合はロールバッ繧?

print(f"Transaction rolled back due to an error: {e}")

finally:

cursor.close()

conn.close()

#カーソ繝?

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

　params: dict形藹??で藹??得#[{'no': 1, 'name': 'Alice',...}, ...]

cursor = conn.cursor(cursor=pymysql.cursors.SSCursor)

　大驥?のデータを効軆??的に藹??得するためにストリーミングで軆??果を処理

cursor.execute(query, params=None)

　cursor.execute("SELECT * FROM test WHERE no = %s", (1,))

　params: プレースホルダーに対応する値のタブルまたはリスト

cursor.executemany(query, param list)

　cursor.executemany("INSERT INTO test (no, name) VALUES (%s, %s)", [(1, 'Alice'), (2, 'Bob')])

　param list:繰り返し実行するパラメータのリストまたはタブルのリスト

cursor.fetchone()

　row = cursor.fetchone()

　#結果があれ縺? (1, 'Alice', "2025-01-01") のような形蠑?縺?1行のみ藹??得

cursor.rowcount

　print(cursor.rowcount) #影響を藹??けた行数を返す

■接続検証用コンテナをビルド (内驛?IPを使うrun逕?)

gcloud builds submit --tag asia-northeast1-docker.pkg.dev/prj/artifact_reg_name/app_name

■IAM？
Cloud SQL設藹??縺?Cloud SQL 管理者 (roles/cloudsql.admin)、Cloud SQL インスタン繧? ユーザ繝? (roles/cloudsql.instanceUser)等縺?IAMが要る?

IAMユーザならいる、ローカ繝?Userなら不要と思繧?れる、ローカルでもCloud SQL Client (roles/cloudsql.client)等は鐔??る

■Cloud SQL MySQL設藹??

【開発環藹??】db_instance01

Enterprise / Sandbox / AsiaNorthEast1 (Tokyo) / Single zone

MySQL ver 8.4

Shared core/1cpu 0.6GB/HDD/10GB(auto increase)

PrivateIP/設藹??縺?nwが必要(下記)/Enable private path

Auto daily backup 7days (1-5AM) / Enable point-in-time recovery

Week1 sun 0-1am/Enable query insights

root PW: 69696969

【本番環藹??】

Enterprise plus? キャッシュ使う?

窶?CloudSQL縺?TFファイルに鐔??載がな縺?てもTFステートファイル縺?PWを含めてしまうためTF化しない

- NW: projects/prj/global/networks/sql-vpc-nw

- Connection name: prj:asia-northeast1 db_instance01

ユーザの臀??成 sql-user/82828282

　PWをコードに入れない、シク繝?Mgrに臀??存

■MySQL

utf8mb4_ja_0900_as_ci_ksを使う？

MySQL 8.0縺?Charset utf8mb4での日本鐔??環藹??で使うCollationで文字比較をしてみる - CLOVER🍀

_ai... アクセントを区別しない (Accent Insensitive)

_as... アクセントを区別する (Accent Sensitive)

_ci... 大文字・藹??文字を区別しない (Case Insensitive)

_cs... 大文字・藹??文字を区別する (Case Sensitive)

_ks... カナを区別する (Kana Sensitive)

_bin... バイナ繝?

utf8mb4_unicode_ciで縺?"ア”と“あ”は同じものとして扱繧?れる

utf8mb4_ja_0900_as_ci_ks で縺?"繧?"≠”あ”となりカタカナとひらがなを譏?確に区別できる

utf8mb4_ja_0900_as_ci_ks ならふりがなを使った並び替えで有蜉?

日本鐔??のデータがメインで觸??索やソートでひらがな・カタカナ・觸??点の区別が必要なら utf8mb4_ja_0900_as_ci_ks が驕?

日本鐔??と英鐔??を混ぜたデータや広く互觸??性を持たせたいなら utf8mb4_unicode_ci の方が無髮?

インデックスをどのカラムに臀??ればいいか迷った時の・??つの設計方針 #DB - Qiita

テーブルレコード数が1万件以上

全臀??のレコード数縺?5%程度に軆??り込めるカーディナリティ饅??さ

WHERE句の選択条件、または軆??合条件

主キーおよびユニークの列には臀??成が不要

インデックスは更新性能を劣化させる

データベースとテーブルの臀??成

CREATE DATABASE db;

USE db;

CREATE TABLE test (

no INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

name VARCHAR(8) NOT NULL,

targetDate TIMESTAMP NOT NULL,

PRIMARY KEY (no),

INDEX index_name (name),

INDEX index_targetDate (targetDate)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_ci_ks;

ENUM型は選択肢で早いがALTERが面倒なの縺?varcharaにする

inquiry_type ENUM('bq', 'pii') NOT NULL

↓

inquiry_type VARCHAR(255) NOT NULL,

VARCHAR(255) (よく使繧?れる最大サイ繧?)

VARCHAR(1024) (長めの文字列)

VARCHAR(4096) (長文向け)

長いテキストを扱うならTEXT型

InnoDB 縺?1行の最大サイズは軆??8KB (8126/バイト)

長さ縺??メール縺?255で良い

サンプルデー繧?

INSERT INTO `table` (`name`, `date`) VALUES ('aaa', '2002-02-23');

ORMapperは面倒なの縺?SQLを使う
ORM Quick Start — SQLAlchemy 2.0 Documentation
【SQLAlchemy】Generic Typesと各遞?DBの型対藹??陦?
SQLAlchemyでのテーブル藹??鄒? #Python - Qiita

■データベースフラ繧?
confが直接変更できなためフラグとしてパラメータを渡せる

データベー繧? フラグを構成する | Cloud SQL for MySQL | Google Cloud

　Cloud SQL studio (コンソール縺?MySQLが使える)

　　MySQLクライアントを使いたいならAuth proxyが必要

　HA構成だとフェールオーバーやリードレプリカ軆??が使える

■NW
Cloud SQLを徹藹??解説！ - G-gen Tech Blog

Cloud SQLが内驛?IPだとサーバレ繧?VPCコネクタ、or 外驛?IPならSQL + auth proxy

内驛?IPで良いの縺?VPCを作る、CloudSQLを内驛?IPで臀??る

サーバレ繧?VPCアクセスコネクタを作る

　vpc: sql-vpc-nw, subnet: sql-vpc-subnet 192.168.77.0/24

　　Gateway 192.168.77.1, Private Google Access On

　　sql-vpc-nw-ip-range 192.168.78.0/24 on cloudSQL

　　run-serverless-vpc-ac 192.168.79.0/28 on Run

ファイアウォールルールでポート (デフォルト縺?3306な縺?) を開謾?

Cloud Run 縺?NW設藹??で、サーバーレ繧? VPCコネクタを選択、ルートオプションとしてすべてのトラフィックを VPC コネクタ軆??由で送信を選択

CloudSQLを30分程度觸??けて起動、接続＞接続テスト

VPC（例: 10.0.0.0/16）

サブネット（Cloud SQL 用・??: 10.10.0.0/24（例: us-central1、VPC内）

サブネット（VPCコネクタ用・??: 10.8.0.0/28（RunからVPCへ通信用、VPC外）

　VPC コネクタのサブネット縺? 10.8.0.0/28 のような藹??さな軆??囲を使用、VPC外だがrun自臀??がVPC外だから？

　VPC コネクタはリージョン単位なので、Cloud Run 縺? Cloud SQL を同じリージョンに配置するのが望ましい

Google Cloudの内驛?NW設鐔??によりVPC内の異なるサブネット間でも通信可閭?

　VPC内なら異なるリージョンのサブネットでもOK（VPC自臀??には軆??囲を設藹??なしでサブネット縺?IPが被らなけれ縺?OKか縺?

　追加の設定なしで、例え縺? us-central1 縺? VM から asia-northeast1 縺? Cloud SQLに直接アクセス藹??

外驛?IPの場合：

アプリがrunならサイドカーコンテナとし縺?Auth Proxyを追加できる

　サイドカーは同Pod内なのでループバックアドレ繧?127.0.0.1あるい縺?localhost:5432 (Auth Proxy起動時に指定したポート) に通信しCloudSQLに接続する

GCE縺?DLし縺?Auth proxyインストールでもいい

　アプリのコネクタ縺?Auth Proxy動いているGCE縺?IP:ポート番号を指定に通信しCloudSQLに接続する

　FWでポートも開けるこ縺?

■run サービスアカウント

run-sql@prj.iam.gserviceaccount.com に藹??要な権限

　Cloud SQL Client (roles/cloudsql.client)

　Run Invoker (roles/run.invoker)

　Compute Network User (roles/compute.networkUser) -VPCコネクタを使用する

runを建てるが、InternalIPのため同プロジェクト同VPC縺?GCE を作成し移動し縺?CURLでテスト

curl -H "Authorization: Bearer $(gcloud auth print-identity-token)" "https://run-sql-test-1212124.asia-northeast1.run.app/test"

■MySQL縺?UUIDを使うか、連番を使うか? > ULIDを使う

UUIDは連番に対し

　セキュリティ臀??より安全、サーバが異なってもユニー繧?

　パフォーマンスが悪い (UUIDをプライマリキーにすると速度が落ちる場合がある)

連番縺?UUIDの両方を振り出してお縺?? > ULIDを使うことにする

MySQL縺?UUIDv4をプライマリキーにするとパフォーマンス問題が起きるのはなぜ？（N回目・??