We claim that any measure $\mu$ of your desired form must be the unique measure that maps every countable set to $0$ and every uncountable Borel set to $\infty$.
Consider the sets \begin{align*}C_1&=\{0\le x<1:\textrm{the decimal expansion of $x$ has only digits 0 or 1}\}\\C_2&=\{0\le x<1:\textrm{the decimal expansion of $x$ has only digits 0 or 2}\}\end{align*}
Standard "Cantor-set" arguments show that
- $C_1,C_2$ are compact subsets of $[0,1)$ which both are uncountable;
- If $x\in C_1+C_2$, then there exists a unique pair $(c_1,c_2)\in C_1\times C_2$ such that $x=c_1+c_2$; (Hint: Consider the decimal expansion of such an $x$.)
- The last item implies ${+}:C_1\times C_2\to (C_1+C_2)$ is a bijection; in particular, $C_1+C_2=\bigsqcup_{c\in C_2}(C_1+c)$ is a partition of $C_1+C_2$ into uncountably many translated copies of $C_1$;
- The last items also imply that $D:=C_1+C_2$ is homeomorphic to the standard Cantor set $C$, and that under this homeomorphism $C$ can be partitioned into uncountably many homeomorphic copies of $C_1$.
Now let $B$ be any uncountable Borel set. We'll show that $\mu(B)=\infty$.
We use the so-called perfect set theorem for Borel sets which says that every uncountable Borel set $B$ contains a subset $B'$ homeomorphic to the standard Cantor set $C$. The final item above shows that $B'$ can then be partitioned into uncountably many homeomorphic copies of $C_1$. We write
$$B\supseteq B'=\bigsqcup_{i\in I}C^i$$
where each $C^i\cong C_1$ is homeomorphic. By this homeomorphism, each $C^i$ is also uncountable compact, and hence $\mu(C^i)>0$. It follows that
$$\mu(B)\ge\sum_{i\in I}\mu(C^i)=\infty$$
since the last sum is an uncountable sum of positive summands.
Addendum, per our previous Discord conversation:
From now on a Borel measure on $\mathbb{R}$ is, as you defined, any measure space $(\mathbb{R},\mathcal{F},\mu)$ where the measurable sets $\mathcal{F}\supseteq\mathbf{B}(\mathbb{R})$ contain all Borel sets and possibly more.
A weird measure is such a measure where the $\mu$-null sets $\textsf{null}_\mu\subseteq\mathcal{F}$ coincide with $[\mathbb{R}]^{\le\aleph_0}\subseteq\mathbf{B}(\mathbb{R})$ the set of all countable subsets of $\mathbb{R}$, and moreover there exists $X\in\mathcal{F}$ measurable with $0<\mu(X)<\infty$.
Claim. The existence of a weird measure is independent of $\textsf{ZFC}$:
- Under $\textsf{ZFC}+\textsf{CH}$, there exists a weird measure $(\mathbb{R},\mathcal{S},\mu)$ with uncountable $X\in\mathcal{S}$ such that $\mu(X)=1$;
- Under $\textsf{ZFC}+\textsf{MA}_{\aleph_1}+\lnot(\textsf{CH})$, there exists no weird measure, and so for every measure space $(\mathbb{R},\mathcal{F},\mu)$ with $\mathbf{B}(\mathbb{R})\subseteq\mathcal{F}$ and $\textsf{null}_\mu=[\mathbb{R}]^{\le\aleph_0}$, and for every $X\in\mathcal{F}$ uncountable, necessarily $\mu(X)=\infty$.
Sketches.
(1) We prove a stronger statement where $\textsf{CH}$ is replaced with $\mathop{\textsf{add}}(\textsf{null})=\mathop{\textsf{cof}}(\textsf{null})=\kappa\in[\aleph_1,\mathfrak{c}]$ and in the definition of $\mu$ we replace with $\textsf{null}_\mu=[\mathbb{R}]^{<\kappa}$, rewording everything else accordingly (eg. for every instance of "countable" we say "less than $\kappa$" instead). Clearly this implies part (1) of the claim.
Using a usual $\mathop{\textsf{add}}(\textsf{null})=\mathop{\textsf{cof}}(\textsf{null})$ construction, take an increasing cofinal sequence $\langle A_i:i<\kappa\rangle$ in the poset $(\textsf{null},{\subseteq})$, and let $X=\{x_i:i<\kappa\}$ with $x_i\notin A_i$ so that $\left|X\cap N\right|<\kappa$ for all Lebesgue null $N$. Since $X$ is non-null, there is a non-null Borel outer envelope $X\subseteq E\in\textbf{B}(\mathbb{R})\smallsetminus\textsf{null}$ such that $E\smallsetminus X$ has inner measure $0$, and we may assume that $E=\mathbb{R}$ by a null-set-preserving Borel isomorphism $E\to\mathbb{R}$, so that we may assume $X\cap A\ne \varnothing$ for every non-null Borel $A$.
Let $\nu$ be the Gaussian probability measure on $\mathbb{R}$, and restrict $\nu$ to $X$ by setting $\nu^*(X\cap B):=\nu(B)$ for every Borel $B$. Note $\nu^*$ is well-defined with $\nu^*(X)=1$ and $\textsf{null}_{\nu^*}=[X]^{<\kappa}$. We let $\mathcal{S}=\mathbf{B}(\mathbb{R})\oplus X$ be the Borel $\sigma$-algebra enriched with $X$, and set
$$\mu(A)=\nu^*(A\cap X)+\begin{cases}0,&\left|A\smallsetminus X\right|<\kappa\\\infty,&\left|A\smallsetminus X\right|\ge\kappa\end{cases}$$
Then $\mu$ satisfies $\textsf{null}_\mu=[\mathbb{R}]^{<\kappa}$, with $\mu(X)=1$ witnessing weirdness. $\square$
(2) It suffices to show every uncountable $X\in\mathcal{F}$ contains a subpartition $X\supseteq\bigsqcup_{i<\omega_1}X_i$ with each $X_i\in\mathcal{F}$ uncountable. Then $\mu(X)\ge\sum_{i<\omega_1}\mu(X_i)=\infty$ works, since $\mu(X_i)>0$ for all $i$.
It's known that $\textsf{MA}_{\aleph_1}$ implies for every $A\sqcup B\subseteq\mathbb{R}$ with $\left|A\right|=\left|B\right|=\aleph_1$, there exists a $G_\delta$ set $A\subseteq C\subseteq\mathbb{R}\smallsetminus B$. My earlier MSE post uses a variation of a proof of this fact.
Take any subpartition $X\supseteq \bigsqcup_{i<\omega_1} A_i$ where each $\left|A_i\right|=\aleph_1$. Take $G_\delta$ sets $\langle C_i:i<\omega_1\rangle$ with $A_i\subseteq C_i\subseteq\mathbb{R}\smallsetminus\bigsqcup_{i<j<\omega_1}A_j$. Then $X_i=X\cap\left(C_i\smallsetminus\bigcup_{j<i}C_j\right)$ works, as $A_i\subseteq X_i$ and $\bigsqcup_{i<\omega_1}X_i\subseteq X$. $\square$
